pivot - SQL Server Forums

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)

pivot

Author

Topic

amaskey
Starting Member

4 Posts

Posted - 2014-04-07 : 15:22:17

sql table
userid criteria value
1 height 60
1 weight 133
1 age 22
1 sex m
2 height 57
2 weight 120
2 age 62
3 height 66
3 weight 170
3 age 30
3 sex f

pivot to
userid height weight age sex
1 60 133 22 m
2 57 120 62 NULL
3 66 170 30 f

problem - userid 2 will not show up in pivoted table since one of rows (sex) is missing from the original sql table.

Thank you

Ayush

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-04-07 : 15:56:24

quote:
Originally posted by amaskey

sql table
userid criteria value
1 height 60
1 weight 133
1 age 22
1 sex m
2 height 57
2 weight 120
2 age 62
3 height 66
3 weight 170
3 age 30
3 sex f

pivot to
userid height weight age sex
1 60 133 22 m
2 57 120 62 NULL
3 66 170 30 f

problem - userid 2 will not show up in pivoted table since one of rows (sex) is missing from the original sql table.

Thank you

Ayush

Are you using the PIVOT operator, or are you using a manual pivot? If you are using the PIVOT operator in the usual way people use it, then userid 2 also will show with a null for the sex column.

If you are using manual pivoting, please post the code you are using.

sqlsaga
Yak Posting Veteran

93 Posts

Posted - 2014-04-07 : 16:46:25

It is acting correctly, it will not show up any data when there is no data present for that column. You need help with the pivot query? see this article I wrote on Pivoting @ http://sqlsaga.com/sql-server/how-to-use-pivot-to-transform-rows-into-columns-in-sql-server/

Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

amaskey
Starting Member

4 Posts

Posted - 2014-04-07 : 23:26:42

Following is the code i am using. I am new to sql and I only know the basics from w3school.

select
doc.userid,
obs1.value,
obs2.value,
obs3.value,
obs4.value

from document as doc
left join obs as obs1 on obs1.id = doc.id
left join obs as obs2 on obs2.id = doc.id
left join obs as obs3 on obs2.id = doc.id
left join obs as obs4 on obs2.id = doc.id

where obs1.criteria = 'height'
and obs2.criteria = 'weight'
and obs3.criteria = 'age'
amd obs4.criteria = 'sex'

Ayush

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2014-04-08 : 00:38:08

CREATE TABLE SqlTable(userid INT,criteria CHAR(10),value VARCHAR(10))
INSERT INTO sqltable VALUES(1,'height','60'),
(1,'weight','133'),
(1,'age','22'),
(1,'sex','m'),
(2,'height','57'),
(2,'weight','120'),
(2,'age','62'),
(3,'height','66'),
(3,'weight','170'),
(3,'age','30'),
(3,'sex','f')

SELECT userid,[height],[weight],[age],[sex] FROM
(SELECT * FROM sqltable)x
PIVOT(MAX(Value) FOR criteria IN ([height],[weight],[age],[sex])) AS Pvt

-- Dynamic Pivot :

DECLARE @Query VARCHAR(MAX),@Result VARCHAR(MAX)
SET @Query = STUFF((SELECT DISTINCT ','+'['+Criteria+']'FROM SqlTable FOR XML PATH('')),1,1,'')

SET @Result = N'SELECT UserId,'+@Query+' FROM
(SELECT * FROM SqlTable)x
PIVOT(MAX(Value) FOR Criteria IN ('+@Query+')) AS pvt'

EXECUTE (@Result)

DROP TABLE SqlTable

Veera

amaskey
Starting Member

4 Posts

Posted - 2014-04-08 : 14:25:36

Can I use similar syntax to pivot 2 tables?

Ayush

sqlsaga
Yak Posting Veteran

93 Posts

Posted - 2014-04-09 : 14:15:48