how to make rows into column using pivot

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources