1st time Pivot Table - 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)

1st time Pivot Table

Author

Topic

redhawk
Starting Member

8 Posts

Posted - 2011-05-03 : 11:11:41

Hi, Appriciate help with creating a Pivot (first time with Pivot):
Having two tables, one with rows containing names and the other contains Values and I need to display Rows Name as Column. I started with the following, but getting only first 3 rows.

SELECT [First Name],[Last Name],[Email]   FROM
(
	select p.ID,p.Name, i.Value, p.Status 
	from dbo.IdentityProperties i
	inner join dbo.Properties p
	on i.PropertyID = p.ID
) DataTable
PIVOT
(
	MIN(Value)
	FOR Name
	IN ([First Name],[Last Name],[Email])
) AS PivotTable

Results:
ID Status First Name Last Name Email
2 1 dddd NULL NULL
3 1 NULL bbbbb NULL
6 1 NULL NULL dddd@bbb.gg.il

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-05-05 : 02:15:29

Please post table structure and sample data.

Vaibhav T

If I cant go back, I want to go fast...

redhawk
Starting Member

8 Posts

Posted - 2011-05-05 : 06:46:04

Tbl IdentityProperties:
Column_name Type
IdentityID int
PropertyID int
Value nvarchar

Date:
IdentityID PropertyID Value
2 2 vvvvv
2 3 qqqq
2 6 nnn@ee.jj.oo
3 2 dddd
3 3 bbbbb
3 6 dddd@bbb.gg.il
10 2 kkkkk
10 3 jjjjjjjj

Tbl Properties
Column_name Type
ID int
Name nvarchar

Data:
ID Name
2 First Name
3 Last Name
4 Middle Name
5 Birth Country
6 Email

Kind regards
Dave

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-05-05 : 13:41:04

Here's one way:


SELECT max(case when p.name = 'First Name' then value end) firstName
       ,max(case when p.name = 'Last Name' then value end) lastName
       ,max(case when p.name = 'email' then value end) email
from IdentityProperties i
inner join Properties p
       on i.PropertyID = p.ID
group by identityid

Be One with the Optimizer
TG

redhawk
Starting Member

8 Posts

Posted - 2011-05-08 : 06:47:50

Many thanks TG.
Can it be created dynamically from the Properties.Name ?

quote:

Originally posted by TG

Here's one way:


SELECT max(case when p.name = 'First Name' then value end) firstName
       ,max(case when p.name = 'Last Name' then value end) lastName
       ,max(case when p.name = 'email' then value end) email
from IdentityProperties i
inner join Properties p
       on i.PropertyID = p.ID
group by identityid

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-05-08 : 21:23:21

yes - try searching this site for "dynamic cross tab". Here is one link with examples as well as a link to an article:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=139658

Be One with the Optimizer
TG

redhawk
Starting Member

8 Posts

Posted - 2011-05-09 : 07:16:33

Cool!!! thanks a lot TG.

Red

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources