SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 pivot
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

amaskey
Starting Member

USA
4 Posts

Posted - 04/07/2014 :  15:22:17  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3568 Posts

Posted - 04/07/2014 :  15:56:24  Show Profile  Reply with Quote
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.
Go to Top of Page

sqlsaga
Yak Posting Veteran

USA
93 Posts

Posted - 04/07/2014 :  16:46:25  Show Profile  Reply with Quote
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.
Go to Top of Page

amaskey
Starting Member

USA
4 Posts

Posted - 04/07/2014 :  23:26:42  Show Profile  Reply with Quote
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
Go to Top of Page

VeeranjaneyuluAnnapureddy
Posting Yak Master

India
164 Posts

Posted - 04/08/2014 :  00:38:08  Show Profile  Reply with Quote
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
Go to Top of Page

amaskey
Starting Member

USA
4 Posts

Posted - 04/08/2014 :  14:25:36  Show Profile  Reply with Quote
Can I use similar syntax to pivot 2 tables?

Ayush
Go to Top of Page

sqlsaga
Yak Posting Veteran

USA
93 Posts

Posted - 04/09/2014 :  14:15:48  Show Profile  Reply with Quote
quote:
Originally posted by amaskey

Can I use similar syntax to pivot 2 tables?

Ayush



Yes, you can pivot multiple tables.

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

amaskey
Starting Member

USA
4 Posts

Posted - 04/15/2014 :  15:50:52  Show Profile  Reply with Quote
Thanks

Ayush
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000