SQL Server Forums
Profile | Register | 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		  New Topic  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

5152 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
47189 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
47189 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  
 New 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