Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 how to make rows into column using pivot

Author  Topic 

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-10-18 : 04:01:02
i have an date like this

Name              VALUE
ClientID M01010001250
InterviewType 1
InterviewDate 7/8/2011
ClientID M01010001260
InterviewType 1
InterviewDate 7/8/2011
ClientID M01010001260
InterviewType 5
InterviewDate 1869-07-01
ClientID M01010001290
InterviewType 1
InterviewDate 7/8/2011


now my out put should be like this


SEQ ClientID InterviewType InterviewDate
1 M01100016550 5 9/9/2011
2 M01100016550 5 9/9/2011
3 M01030000680 5 9/9/2011


i have written a query using pivot :

SELECT SEQ,ClientID,InterviewType,InterviewDate
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY NAME,VALUE ORDER BY NAME,VALUE) AS SEQ,NAME,VALUE
FROM Table1) DT
PIVOT (MAX(VALUE)FOR NAME IN(ClientID,InterviewType,InterviewDate))DT1
ORDER BY SEQ

even though i am using row_number it is not giving desired output suggest me


P.V.P.MOhan

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-10-18 : 05:32:48
i think the follwoing is the expected output.....

DECLARE @data TABLE( Name varchar(50), VALUE varchar(100))
insert into @data
SELECT 'ClientID', 'M01010001250' union all
SELECT 'InterviewType', '1' union all
SELECT 'InterviewDate', '7/8/2011' union all
SELECT 'ClientID', 'M01010001260' union all
SELECT 'InterviewType', '1' union all
SELECT 'InterviewDate', '7/8/2011' union all
SELECT 'ClientID', 'M01010001260' union all
SELECT 'InterviewType', '5' union all
SELECT 'InterviewDate', '1869-07-01' union all
SELECT 'ClientID', 'M01010001290' union all
SELECT 'InterviewType', '1' union all
SELECT 'InterviewDate', '7/8/2011'

SELECT SEQ,ClientID,InterviewType,InterviewDate
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY (SELECT 1)) AS SEQ,NAME,VALUE
FROM @data) DT
PIVOT (MAX(VALUE)FOR NAME IN(ClientID,InterviewType,InterviewDate))DT1
ORDER BY SEQ

/* OUTPUT:
SEQ ClientID InterviewType InterviewDate
1 M01010001250 5 1869-07-01
2 M01010001260 1 7/8/2011
3 M01010001260 1 7/8/2011
4 M01010001290 1 7/8/2011
*/


--
Chandu
Go to Top of Page

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-10-18 : 09:36:39
Hi chandu i have written like this

select [Row],ClientID,InterviewType,CONVERT(varchar(10), CONVERT(datetime, Interviewdate, 101), 103)
from (select NAME,VALUE,
CEILING((ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY (SELECT 1)) - 1) / 3) as [Row]
from gabasics t) t
PIVOT (max(value) for name in (ClientID, InterviewType, InterviewDate)) p

but your query is more accurate ....thanks

P.V.P.MOhan
Go to Top of Page
   

- Advertisement -