T-SQL Question

Question

T-SQL Question

SQLSeTTeR

SSCertifiable

Points: 5481
More actions
January 12, 2011 at 3:59 pm

#243647

How can I turn this table:
10032 64 12/9/2010
10032 65 155
10196 64 12/22/2010
10196 65 90
10199 64 12/22/2010
10199 65 200
10200 64 12/22/2010
10200 65 123
to this table
ID [64] [65]
10032 12/9/2010 155
10196 12/22/2010 90
10199 12/22/2010 200
10200 12/22/2010 123
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

Its called a CROSSTAB and I believe SQL 2008 has a function for it.. Do a Books Online Search and you should get syntax..

CEWII

SQLSeTTeR SSCertifiable Points: 5481 More actions · Answer 2

I was hoping I could get a sample query with my data.

WayneS SSC Guru Points: 95460 More actions · Answer 3

SELECT ID,

[64] = MAX(CASE WHEN UnknownColumnName = 64 THEN AnotherUnknownColumnName ELSE NULL END),

[65] = MAX(CASE WHEN UnknownColumnName = 65 THEN AnotherUnknownColumnName ELSE NULL END)

FROM YetAnotherTableWherePosterWantsAQueryButDidntSuppleAnyTableStructureInformation

GROUP BY ID

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

mister.magoo SSC-Forever Points: 47174 More actions · Answer 4

WayneS (1/12/2011)
SELECT ID, [64] = MAX(CASE WHEN UnknownColumnName = 64 THEN AnotherUnknownColumnName ELSE NULL END), [65] = MAX(CASE WHEN UnknownColumnName = 65 THEN AnotherUnknownColumnName ELSE NULL END) FROM YetAnotherTableWherePosterWantsAQueryButDidntSuppleAnyTableStructureInformation
GROUP BY ID

Nice table/columns ! 😛

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

SQLSeTTeR SSCertifiable Points: 5481 More actions · Answer 5

My apologies, I didn't have that information either. Thank you for the reply.

SQLSeTTeR SSCertifiable Points: 5481 More actions · Answer 6

The issue is the code needs to be dynamic as it relates to column 2 (values 64, 65 in this example)

Thanks for all your help!

WayneS SSC Guru Points: 95460 More actions · Answer 7

mister.magoo (1/12/2011)
WayneS (1/12/2011)
SELECT ID, [64] = MAX(CASE WHEN UnknownColumnName = 64 THEN AnotherUnknownColumnName ELSE NULL END), [65] = MAX(CASE WHEN UnknownColumnName = 65 THEN AnotherUnknownColumnName ELSE NULL END) FROM YetAnotherTableWherePosterWantsAQueryButDidntSuppleAnyTableStructureInformation
GROUP BY ID
Nice table/columns ! 😛

Ya gotta play the hand that was dealt to ya! :-D:w00t:

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95460 More actions · Answer 8

SQLSeTTeR (1/12/2011)
The issue is the code needs to be dynamic as it relates to column 2 (values 64, 65 in this example)
Thanks for all your help!

What I did isn't really dynamic - more like cross-tab/pivot. Does it handle your situation? If so, you can get a better understanding by reading the two cross-tab/pivot table links in my signature!

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

SQLSeTTeR SSCertifiable Points: 5481 More actions · Answer 9

The problem why I can't use the Pivot function requires the value to be an aggregate function..these are strings.