Pivoting rows to columns 1

[sds814]

I've googled and searched in tek-tips for this issue, but haven't found the answer. I have row data that I need to pivot as columns. Here is sample data:

code codevalue
ProductID 1
ProductName A
ProductID 2
ProductName B

Which I need to have as
ProductID ProductName
1 A
2 B

I'm trying to get the correct syntax:

SELECT ProductID
    ,ProductName
FROM 
(SELECT ProductID
    ,ProductName
FROM Product) AS SourceTable
PIVOT
(for ProductID, ProductName in ([0], [1])) AS PivotTable;

Thanks for the help!

 

[gmmastros]

Unfortunately, your sample data is not good enough to do what you want. There should be another column in your sample data that allows you to identify a product, or more specifically, which rows (combined) identify a single product. Something like this:

[tt]
ID code codevalue
1 ProductID 1
1 ProductName A
2 ProductID 2
2 ProductName B
[/tt]

Without that other column, there is no way to know that ProductId 1 is associated with Product Name A. This is necessary so that we can show that data on the same row.

Make sense?

By the way, when you post data, it's best to put tt tags around it so that columns of data line up. Ex:

[ignore][tt]
ID code codevalue
1 ProductID 1
1 ProductName A
2 ProductID 2
2 ProductName B
[/tt][/ignore]

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sds814]

That does make sense. In my real data I do have a column that will tell me Product ID 1 is for Product Name A. So what would be my query if my sample data was:
[tt]
ID code codevalue
1 ProductID 1
1 ProductName A
2 ProductID 2
2 ProductName B
[/tt]

 

[gmmastros]

To be honest, I strongly dislike the PIVOT operator. I find it confusing and difficult to understand (maybe I just don't get it).

The following query will work on all versions of SQL (not just sql2005 and newer).

Select ID, 
       MAX(Case When Code = 'ProductId' Then CodeValue End) As ProductId,
       MAX(Case When Code = 'ProductName' Then CodeValue End) As ProductName
From   [!]YourTableName[/!]
Group BY ID

If you truly want to use the pivot operator...

Select *
From   [!]YourTableName[/!] T
PIVOT(Max(CodeValue) For Code in ([ProductId], [ProductName])) As PivotTable

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sds814]

Thanks George. I agree the pivot is confusing.

I tried the tt in my previous post, but it didn't work. I'll try again when I post another message.

 

[gmmastros]

The tt did work. When you use tt in a post, you can use spaces to line up columns of data. Really... it's just a visual thing.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom