How to convert rows into columns?

i think this will help you..

table design for me is like this

table name is::CandidateTemp

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='email' and b.FieldName='skill' and c.FieldName='name'

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

I have a table "parameterData" 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

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

----------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

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

-- CK

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

http://www.logiclabz.com/sql-server/...ing-pivot.aspx

Thanks a ton!

Simplest way to achieve this..

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

http://blog.programmingsolution.net/...dynamic-query/