How to convert rows into columns?

[dotnetguru]

Hi SMART GUYS,

Please help me write a query. Actually I want to convert my rows into columns. Can anyone kindly give me the query to do it? My rows are about employees. There can be any number of employees (in the following table I have 3 employees ABC, EFG, WRI) with any number of records for each employee (there can be 20, 30 records each), but all employees have the same number of records (in the following table each employee has 3 records). My table is:


USERID Name Role Total
100 ABC 1 0
100 ABC 1 0
100 ABC 1 1
101 EFG 1 0
101 EFG 1 2
101 EFG 1 1
102 WRI 2 1
102 WRI 2 0
102 WRI 2 1

I want to convert the above table in the following form:

USERID Name Role Total1 Total2 Total3
100 ABC 1 0 0 1
101 EFG 1 0 2 1
102 WRI 2 1 0 1

Columns [USERID], [Name], [Role] are unique for each row but Column [Total] is dynamic (in above case, 3 [Total] Columns Total1, Total2, Total3) and depends on the number of records against each employee. If there were 30 records for each employee, the Column [Total] will be (Total1, Total2, Total3…Total30) , I have mentioned above that all employees will have the same number of records.

I have even used the Pivot Operator in SQL Server 2005 but it always end up in an error about the syntax proceeding the PIVOT word. I just need a query with/without the PIVOT operator.

I look forward to your reply. I will be highly obliged.

Regards...

[anoopsebastian]

i think this will help you..

table design for me is like this

table name is::CandidateTe mp

id FieldName Value

1 email aaa
1 skill c
1 name AAAAA
2 email aaa
2 skill c,java
2 name AAAAA
5 email aaa
5 skill c
5 name GGGG
9 email iii
9 skill c,vb,java
9 name AAAA



and i want this format

id email skill name

1 aaa c AAAAA
2 aaa c,java AAAAA
5 aaa c GGGG
9 iii c,vb,java AAAA


Query for this is



select a.id as id, a.Value as email, b.Value as skill, c.Value as name from CandidateTemp a,CandidateTemp b,CandidateTemp c where a.id=b.id and b.id=c.id
and a.FieldName='em ail' and b.FieldName='sk ill' and c.FieldName='na me'

[scripto]

Hi SMART GUYS,

Regards...

This was a nice challenge - i created a new_employee table to hold the results.
here it is based on your specific requirements:

CREATE TABLE [dbo].[new_employees] (
[userid] [int] NOT NULL ,
[name] [varchar] (50) NOT NULL ,
[role] [int] NOT NULL
) ON [PRIMARY]
GO

-- backup your employees table first
alter table employees add [ID] int identity(1,1) not null
go

declare @max int, @min int
set @max = (select max(id) from employees)
set @min = (select min(id) from employees)

declare @totcols int, @counter int
set @totcols = (select count(*) from employees where userid = 100)
set @counter = 1

declare @str varchar(50)
declare @sqlStr nvarchar(255)


while @counter <= @totcols
begin
set @str = 'total'+ convert(varchar (5),@counter)

set @sqlStr = N'alter table new_employees add '+ @str + ' int null'
exec sp_executesql @sqlStr


set @counter = @counter + 1
set @str = ''
set @sqlStr = ''
end

insert into new_employees (USERID, Name, Role)
select distinct USERID, Name, Role from employees


while @min <= @max
begin
set @counter = 1
while @counter <= @totcols
begin
set @str = 'total'+ convert(varchar (5),@counter)

set @sqlStr = N'update new_employees set ' + @str + ' = (select total from employees where id = ' + convert(varchar (5),@min) + ')' +
' where userid = (select userid from employees where id = ' + convert(varchar (5),@min) + ')'
exec sp_executesql @sqlStr

set @counter = @counter + 1
set @min = @min + 1
set @str = ''
set @sqlStr = ''

end


end



select * from new_employees

[puat133]

I have a table "parameterD ata" look like this:

ParID TimeStamp Scale Value
1 8/9/2007 10:48:46 AM 10 1500
2 8/9/2007 10:48:46 AM 10 1610
3 8/9/2007 10:48:46 AM 10 1640
4 8/9/2007 10:48:46 AM 10 1580
5 8/9/2007 10:48:46 AM 0.1 219.2
6 8/9/2007 10:48:46 AM 0.1 332.6
7 8/9/2007 10:48:46 AM 0.1 219.3
8 8/9/2007 10:48:46 AM 0.1 257
1 8/9/2007 10:50:06 AM 10 1570
2 8/9/2007 10:50:06 AM 10 1700
3 8/9/2007 10:50:06 AM 10 1470
4 8/9/2007 10:50:06 AM 10 1570
5 8/9/2007 10:50:06 AM 0.1 219.6
6 8/9/2007 10:50:06 AM 0.1 332.6
7 8/9/2007 10:50:06 AM 0.1 219.2
8 8/9/2007 10:50:06 AM 0.1 257

Timestamp value for all-eight row (parID 1-8) is same
I want to have a table where column like this
TimeStamp 1 2 3 4 5 6 7 8


How do i do this?

thanx

[Kamboj]

Here is a Query:-
SELECT USERID,NAME,ROL E,MAX(DECODE(R, 1,TOTAL)) TOT1,
MAX(DECODE(R,2, TOTAL)) TOT2,
MAX(DECODE(R,3, TOTAL)) TOT3
FROM (SELECT USERID,NAME,ROL E,TOTAL
,ROW_NUMBER() OVER(PARTITION BY USERID ORDER BY USERID) R
FROM T3)
GROUP BY USERID,NAME,ROL E

----------output----------------------------------------------------------------------------

USERID NAME ROLE TOT1 TOT2 TOT3
---------- ----- ---------- ---------- ---------- ----------
100 ABC 1 0 0 1
101 EFG 1 0 2 1
102 WRI 2 1 0 1

[ck9663]

If you're using SQL 2005, you're in luck. Try here

-- CK

[webtips]

U can use PIVOT in Sql Server 2005

PIVOT relational operator in sql server 2005 can be used to convert rows to columns

http://www.logiclabz.com/sql-server/transpose-rows-to-columns-in-sql-server-2005-using-pivot.aspx

[TSKN]

Thanks a ton!

[Sandeep M]

Simplest way to achieve this..

CREATE TABLE [dbo].[test10](
	[custID] [int] NULL,
	[Question] [varchar](50) NULL,
	[answer] [varchar](20) NULL
) ON [PRIMARY]

GO

INSERT INTO test10 values(1000,	'AAA',	'1')
INSERT INTO test10 values(1000,	'BBB',	'2')
INSERT INTO test10 values(1000,	'CCC',	'3')
INSERT INTO test10 values(1001,	'AAA',	'2')
INSERT INTO test10 values(1001,	'BBB',	'3')
INSERT INTO test10 values(1001,	'CCC',	'3')
INSERT INTO test10 values(1000,	'DDD',	'6')

GO

Declare @t VARCHAR(10)
Declare @A VARCHAR(1000)
Declare @B VARCHAR(1000)
set @A='SELECT CustID'
SET @B='('
SELECT @A=@A+',['+Question+'] as ' +Question,@B=@B+'['+Question+'],' FROM (SELECT DISTINCT QUESTION FROM TEST10) cur
-- removing last ',' from both variables
SET @B=SUBSTRING(@B,1,LEN(@B)-1)
SET @A=@A+ + ' FROM (SELECT CustID, Answer, Question FROM test10) s  PIVOT (max(answer) FOR Question IN ' +@B+')) p ORDER BY [CUSTID]; '
exec(@A);

[maeenul]

see the following post for this. you can convert into as many columns needed based on the possible row values

http://blog.programmingsolution.net/sql-server-2008/sql-server-pivot-converting-rows-to-columns-with-dynamic-query/