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

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Converting Grouped Non-Numeric Rows to Columns

Author  Topic 

BobRoberts
Posting Yak Master

109 Posts

Posted - 2013-06-15 : 15:06:08

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

52326 Posts

Posted - 2013-06-15 : 15:17:40
[code]
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
[/code]

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

BobRoberts
Posting Yak Master

109 Posts

Posted - 2013-06-15 : 16:03:55
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

52326 Posts

Posted - 2013-06-16 : 14:19:05
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
   

- Advertisement -