pivot table with string values? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 table with string values?

Author

Topic

skwash
Starting Member

2 Posts

Posted - 2009-07-17 : 17:17:44

Would it be possible to create a pivot table using non-aggregate data?

Source table would look like:

ID Type Value
---------------------
1 Name Sam
1 Sex M
1 Phone 123
2 Name Sally
2 Sex F
3 Name Pat
4 Name Tom
4 Phone 456

Result would look like this:

ID Name Sex Phone
---------------------------
1 Sam M 123
2 Sally F Null
3 Pat Null Null
4 Tom Null 456

Does that make sense? I am open to doing the "pivot table" sort of action in Excel or even installing reporting services if I can get it at all.

Thanks,
Josh

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-17 : 17:30:19

Yes, you can, but it will be awful slow compared to the PIVOT function.

N 56°04'39.26"
E 12°55'05.63"

skwash
Starting Member

2 Posts

Posted - 2009-07-17 : 17:55:46

What would you recommend? PIVOT requires aggregate functions correct?

The only way can think of doing it is through a big nasty dynamic sql statement.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-18 : 00:30:02

[code]
declare @sample table
(
ID int,
Type varchar(10),
Value varchar(10)
)
insert into @sample
select 1, 'Name', 'Sam' union all
select 1, 'Sex', 'M' union all
select 1, 'Phone', '123' union all
select 2, 'Name', 'Sally' union all
select 2, 'Sex', 'F' union all
select 3, 'Name', 'Pat' union all
select 4, 'Name', 'Tom' union all
select 4, 'Phone', '456'

select *
from @sample s
pivot
(
max(Value)
for Type in ([Name], [Sex], [Phone])
) p
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-18 : 02:10:45

or simply do


SELECT ID,
MAX(CASE WHEN Type='Name' THEN Value ELSE NULL END) AS [Name],
MAX(CASE WHEN Type='Sex' THEN Value ELSE NULL END) AS [Sex],
MAX(CASE WHEN Type='Phone' THEN Value ELSE NULL END) AS [Phone]
FROM Table
GROUP BY ID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources