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
 General SQL Server Forums
 New to SQL Server Programming
 Converting Grouped Non-Numeric Rows to Columns
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

BobRoberts
Posting Yak Master

USA
107 Posts

Posted - 06/15/2013 :  15:06:08  Show Profile  Reply with Quote

I want to categorize the descriptions by status code and put them in the appropriate column. I can't use anything with a PIVOT statement, because the code may be run in a SQL that doesn't yet support PIVOT.


TBL_TESTABC:


Item_ID.......Status_Code........Description
111111........A..................DESCR ONE
111111........B..................DESCR TWO
222222........C..................DESCR THREE
333333........B..................DESCR FOUR
333333........C..................DESCR FIVE
333333........A..................DESCR SIX
444444........B..................DESCR SEVEN
444444........C..................DESCR EIGHT

I want the query to yield:


Item_ID.......Stat_A_Desc.....Stat_B_Desc.....Stat_C_Desc.....
111111........DESCR ONE.......DESCR TWO.......................
222222........................................DESCR THREE.....
333333........DESCR SIX.......DESCR FOUR......DESCR FIVE......
444444........................DESCR SEVEN.....DESCR EIGHT.....

If, instead of Description, the right column were Quantity, and its values were numbers, I would know how to do it with something like:

SELECT ItemID,
     SUM(CASE WHEN StatusCode = 'A' THEN Quantity ELSE 0 END) As Stat_A_Qty,
     SUM(CASE WHEN StatusCode = 'B' THEN Quantity ELSE 0 END) As Stat_B_Qty,
     SUM(CASE WHEN StatusCode = 'C' THEN Quantity ELSE 0 END) As Stat_C_Qty 
FROM [TEST].[dbo].[TBL_TESTABC_Q]
GROUP BY ItemID
ORDER BY ItemID


You'd think it would be possible for SQL to support a kind of concatenate aggregate function. But you may be asking yourself, "but what would such a function do with something like:"


111111........A..................DESCR ONE
111111........B..................DESCR EIGHT 
111111........B..................DESCR NINE
111111........B..................DESCR TEN


Such a dream function might have a parameter to include a separator character string, for example:

AGGCONCAT(Expression,Expression)

Which using the example would look like:

AGGCONCAT(CASE WHEN StatusCode = 'B' THEN Description ELSE '' END,';') As Stat_B_Desc,

When used like the above quantity query, it would yield:

111111....DESCR ONE.......DESCR EIGHT;DESCR NINE;DESCR TEN

Well, I can always dream!


Here is a script to create the test data:


USE [TEST]
GO
/****** Object:  Table [dbo].[TBL_TESTABC]    Script Date: 06/15/2013 12:12:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TBL_TESTABC](
	[ItemID] [varchar](50) NULL,
	[StatusCode] [varchar](50) NULL,
	[Description] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[TBL_TESTABC] ([ItemID], [StatusCode], [Description]) VALUES (N'111111', N'A', N'DESCR ONE')
INSERT [dbo].[TBL_TESTABC] ([ItemID], [StatusCode], [Description]) VALUES (N'111111', N'B', N'DESCR TWO')
INSERT [dbo].[TBL_TESTABC] ([ItemID], [StatusCode], [Description]) VALUES (N'222222', N'C', N'DESCR THREE')
INSERT [dbo].[TBL_TESTABC] ([ItemID], [StatusCode], [Description]) VALUES (N'333333', N'B', N'DESCR FOUR')
INSERT [dbo].[TBL_TESTABC] ([ItemID], [StatusCode], [Description]) VALUES (N'333333', N'C', N'DESCR FIVE')
INSERT [dbo].[TBL_TESTABC] ([ItemID], [StatusCode], [Description]) VALUES (N'333333', N'A', N'DESCR SIX')
INSERT [dbo].[TBL_TESTABC] ([ItemID], [StatusCode], [Description]) VALUES (N'444444', N'B', N'DESCR SEVEN')
INSERT [dbo].[TBL_TESTABC] ([ItemID], [StatusCode], [Description]) VALUES (N'444444', N'C', N'DESCR EIGHT')

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 06/15/2013 :  15:17:40  Show Profile  Reply with Quote

SELECT ItemID,
MAX(CASE WHEN StatusCode = 'A' THEN Description END) AS Status_A_Descrption,
MAX(CASE WHEN StatusCode = 'B' THEN Description END) AS Status_B_Descrption,
MAX(CASE WHEN StatusCode = 'C' THEN Description END) AS Status_C_Descrption
FROM table
GROUP BY ItemID


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

BobRoberts
Posting Yak Master

USA
107 Posts

Posted - 06/15/2013 :  16:03:55  Show Profile  Reply with Quote
Thanks very much - I didn't know, or maybe forgot that the MAX() works with non-numeric. After all, there's a reason I'm posting in the New to SQL Server Programming forum.


quote:
Originally posted by visakh16


SELECT ItemID,
MAX(CASE WHEN StatusCode = 'A' THEN Description END) AS Status_A_Descrption,
MAX(CASE WHEN StatusCode = 'B' THEN Description END) AS Status_B_Descrption,
MAX(CASE WHEN StatusCode = 'C' THEN Description END) AS Status_C_Descrption
FROM table
GROUP BY ItemID


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 06/16/2013 :  14:19:05  Show Profile  Reply with Quote
no problem...it happens to all
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
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.06 seconds. Powered By: Snitz Forums 2000