Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Convert row data to column
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

andy_iimc
Starting Member

5 Posts

Posted - 10/05/2012 :  09:02:13  Show Profile  Reply with Quote
Hi, I require to convert data in the following structure:
StoreCode | FieldName | FieldValue
S001 | StoreName | 100 Store
S001 | OwnerName | Blah
S001 | ..... | ...
S002 | StoreName | 150 Store
S002 | OwnerName | Me
S002 | ... | ...
...

to:
StoreCode | StoreName | Owner Name
S001 | 100 Store | Blah
S002 | 150 Store | Me

How do I go about doing this? I couldn't get the Case-When-Then-End to work. Any help deeply appreciated!

Edited by - andy_iimc on 10/05/2012 09:06:28

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/05/2012 :  10:21:51  Show Profile  Reply with Quote
If you have only those two columns (or a set of known columns) that you want to pivot, you can use the PIVOT operator in SQL 2005 or later. The code would be like this:
SELECT
	*
FROM
YourTable 
PIVOT (MAX(FieldValue) FOR FieldName IN ([StoreName],[OwnerName]))P
If you don't know the columns in advance, you will need to use dynamic pivot. See Madhivanan's blog here: http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx
Go to Top of Page

andy_iimc
Starting Member

5 Posts

Posted - 10/06/2012 :  03:14:00  Show Profile  Reply with Quote
I created the table Test and some data as follows:

CREATE TABLE [dbo].[Test](
[StoreCode] [varchar](10) NULL,
[FieldName] [varchar](20) NULL,
[FieldValue] [varchar](50) NULL,
[StoreAttrNo] [varchar](50) NULL,
[NewFieldValue] [varchar](50) NULL,
[Flag] [int] NULL,
[Report] [int] NULL
) ON [PRIMARY]
GO

GO
INSERT [dbo].[Test] ([StoreCode], [FieldName], [FieldValue], [StoreAttrNo], [NewFieldValue], [Flag], [Report]) VALUES (N'S001', N'StoreName', N'Top100', N'0', NULL, 2, 3)
INSERT [dbo].[Test] ([StoreCode], [FieldName], [FieldValue], [StoreAttrNo], [NewFieldValue], [Flag], [Report]) VALUES (N'S001', N'OwnerName', N'Shivprasad', N'1', N'Shyam', NULL, NULL)
INSERT [dbo].[Test] ([StoreCode], [FieldName], [FieldValue], [StoreAttrNo], [NewFieldValue], [Flag], [Report]) VALUES (N'S002', N'StoreName', N'Top200', N'0', NULL, NULL, 3)
INSERT [dbo].[Test] ([StoreCode], [FieldName], [FieldValue], [StoreAttrNo], [NewFieldValue], [Flag], [Report]) VALUES (N'S002', N'OwnerName', N'Chandra', N'1', NULL, 2, 3)
INSERT [dbo].[Test] ([StoreCode], [FieldName], [FieldValue], [StoreAttrNo], [NewFieldValue], [Flag], [Report]) VALUES (N'S003', N'StoreName', N'Top300', N'0', N'Top3', NULL, NULL)
INSERT [dbo].[Test] ([StoreCode], [FieldName], [FieldValue], [StoreAttrNo], [NewFieldValue], [Flag], [Report]) VALUES (N'S003', N'OwnerName', N'Riya', N'1', N'Keshav', 2, 3)

I ran the query as suggested:

SELECT StoreName, OwnerName
FROM
Test
PIVOT (MAX(FieldValue) FOR FieldName IN ([StoreName],[OwnerName]))P

This is the result I got:

StoreName | OwnerName
Top100 | NULL
NULL |Shivprasad
Top200 |NULL
NULL |Chandra
Top300 |NULL
NULL |Riya

Expected:

StoreName | OwnerName
Top100 | Shivprasad
Top200 | Chandra
Top300 | Riya

Thanks for your response sunitabeck! I suppose I left my initial question incomplete. I have about 29 attributes in my actual table for every store code. Hence, it is not dynamic! I am using SQL Server 2008 R2. How do I get to the Expected results?
Go to Top of Page

andy_iimc
Starting Member

5 Posts

Posted - 10/06/2012 :  05:25:00  Show Profile  Reply with Quote
This did the trick :)

Select StoreCode,
MAX(Case When FieldName = 'StoreName' Then FieldValue Else Null End),
MAX(Case When FieldName = 'OwnerName' Then FieldValue Else Null End)
From Test
Group By StoreCode
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 10/06/2012 :  12:07:36  Show Profile  Reply with Quote
you will get same result from PIVOT too


SELECT StoreName, OwnerName
FROM (SELECT StoreCode,[FieldName], [FieldValue]
FROM Test)t
PIVOT (MAX(FieldValue) FOR FieldName IN ([StoreName],[OwnerName]))P


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

andy_iimc
Starting Member

5 Posts

Posted - 10/08/2012 :  01:56:10  Show Profile  Reply with Quote
You are spot on Visakh - your query worked as well! Follow up question from me .. why is that additional Inner query required?

This works:

SELECT StoreName, OwnerName
FROM (SELECT StoreCode,[FieldName], [FieldValue]
FROM Test
)t
PIVOT (MAX(FieldValue) FOR FieldName IN ([StoreName],[OwnerName]))P

This doesn't:

SELECT StoreName, OwnerName
FROM Test
PIVOT (MAX(FieldValue) FOR FieldName IN ([StoreName],[OwnerName]))P

Neither does this:

SELECT StoreName, OwnerName
FROM (SELECT *
FROM Test
)t
PIVOT (MAX(FieldValue) FOR FieldName IN ([StoreName],[OwnerName]))P
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 10/08/2012 :  20:14:48  Show Profile  Reply with Quote
because it involves other columns which has unique values (in this case StrAttrNo) which causes additional rows to appear in output

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000