Basic Pivot Question - 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)

Basic Pivot Question

Author

Topic

Kristen
Test

22859 Posts

Posted - 2010-07-13 : 09:48:47

Never used Pivot before. First attempt is not going well


DECLARE	@TestData TABLE
(
	MyGUID uniqueidentifier,
	MyAttribute varchar(10)
)

INSERT INTO @TestData
SELECT	'A5E603C0-A3D4-46CB-B78A-361B09236376', 'CAT' UNION ALL
SELECT	'A5E603C0-A3D4-46CB-B78A-361B09236376', 'DOG' UNION ALL
--
SELECT	'95BF3BE2-CD23-468C-9A32-8373A5884381', 'CAT' UNION ALL
SELECT	'95BF3BE2-CD23-468C-9A32-8373A5884381', 'DOG' UNION ALL
--
SELECT	'4CBDC8F6-1AFB-49F6-B52D-D33667BB37A1', 'CAT' UNION ALL
--
SELECT	'AD0AB4E4-284B-4452-9693-FD5B0FCE7AFD', 'DOG'

SELECT	*
FROM	@TestData
ORDER BY MyGUID, MyAttribute

What I want is something like this (I just want to know that CAT or DOG exists for a given MyGUID; can be 1 / NULL / anything similar)


MyGUID                               CAT DOG 
------------------------------------ --- ---
A5E603C0-A3D4-46CB-B78A-361B09236376 1   1
95BF3BE2-CD23-468C-9A32-8373A5884381 1   1
4CBDC8F6-1AFB-49F6-B52D-D33667BB37A1 1
AD0AB4E4-284B-4452-9693-FD5B0FCE7AFD     1

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-13 : 10:01:23

[code]
SELECT *
FROM @TestData
pivot
(
count(MyAttribute)
for MyAttribute in ([CAT], [DOG])
) p
order by MyGUID
[/code]

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

Kristen
Test

22859 Posts

Posted - 2010-07-13 : 10:59:45

Ah! Thanks, that's solved it.

I had been trying:


SELECT	*
FROM	@TestData
	pivot
	(
		count(MyGUID)
		for	MyAttribute in ([CAT], [DOG])
	) p
order by MyGUID

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-07-13 : 12:24:43

the following is nice stuff for dynamic pivot

http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Kristen
Test

22859 Posts

Posted - 2010-07-13 : 12:26:42

Thanks yosiasz, I did have a quick look at Madhi's blog before I posted, but I figured I needed something simple to get me started!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources