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........Description111111........A..................DESCR ONE111111........B..................DESCR TWO222222........C..................DESCR THREE333333........B..................DESCR FOUR333333........C..................DESCR FIVE333333........A..................DESCR SIX444444........B..................DESCR SEVEN444444........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 ItemIDORDER 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 ONE111111........B..................DESCR EIGHT 111111........B..................DESCR NINE111111........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 ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[TBL_TESTABC]( [ItemID] [varchar](50) NULL, [StatusCode] [varchar](50) NULL, [Description] [varchar](50) NULL) ON [PRIMARY]GOSET ANSI_PADDING OFFGOINSERT [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')