SQL conversion values ​​returned in 11 rows, in 89 columns

Question

I need help translating rows of data into columns.

Each of my rows consists of 8 columns, all of which I would like to include in the final list of columns.

Typically, I would write a select statement similar to the one below to return all pricing lines (about 11) related to a specific ProductID

 SELECT ProductID, PricingID, COSBeginQty , COSCOLCode, COSTrade, COSTypeOfPrice, COSIsActive, COSPrice, COSPriceTAG, RowIndex FROM Pricing Where ProductID = XXXXX 

Each of my returned rows has what I want to display all the returned values:

 ProductID, CosBeginQty1 COSBeginQty1 COSCOLCode1 COSTrade1 COSTypeOfPrice1 COSIsActive1 COSPrice1 COSPriceTAG1 CosBeginQty2 COSBeginQty2 COSCOLCode2 COSTrade2 COSTypeOfPrice2 COSIsActive2 COSPrice2 COSPriceTAG2 CosBeginQty3, COSBeginQty3 COSCOLCode3 COSTrade3 COSTypeOfPrice3 COSIsActive3 COSPrice3 COSPriceTAG3 . . . . 

.,. (counting the values ​​that I need from all 11 rows, total 89) columns

Here is a look at the script for my table

 CREATE TABLE [dbo].[Pricing]( [ProductID] [uniqueidentifier] NOT NULL, [PricingID] [uniqueidentifier] NULL, [COSBeginQty] [int] NULL, [COSCOLCode] [nvarchar](50) NULL, [COSTrade] [nvarchar](50) NULL, [COSTypeOfPrice] [nchar](10) NULL, [COSIsActive] [bit] NULL, [COSPrice] [decimal](12, 3) NULL, [COSPriceTAG] [uniqueidentifier] NULL, [RowIndex] [int] NULL, [COSCreateDate] [datetime] NULL, [COSLastModifiedDate] [datetime] NULL, CONSTRAINT [PK_Pricing] PRIMARY KEY CLUSTERED ( [ProductID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO INSERT [dbo].[Pricing] ([ProductID], [PricingID], [COSBeginQty], [COSCOLCode], [COSTrade], [COSTypeOfPrice], [COSIsActive], [COSPrice], [COSPriceTAG], [RowIndex], [COSCreateDate], [COSLastModifiedDate]) VALUES (N'99533f6e-218f-48ef-bf01-03ea7808f9b1', N'5d8de11a-2399-4c68-87cd-4969827bed27', 7, N'MAN', N'T1', N'B ', 1, CAST(23.400 AS Decimal(12, 3)), N'bf1d79bf-a60d-4603-8c2d-04492e9e1575', 1, NULL, NULL) GO INSERT [dbo].[Pricing] ([ProductID], [PricingID], [COSBeginQty], [COSCOLCode], [COSTrade], [COSTypeOfPrice], [COSIsActive], [COSPrice], [COSPriceTAG], [RowIndex], [COSCreateDate], [COSLastModifiedDate]) VALUES (N'94d67d13-e4e6-4360-9b7a-1cef7d997297', N'5ba0a6e9-53ed-4b9c-a9eb-2b09c41eb56c', 2, N'MAN', N'T2', N'A ', 1, CAST(3456.234 AS Decimal(12, 3)), N'6ce7d421-e49a-469f-ae4c-a8bbb2f432fc', 3, NULL, NULL) GO INSERT [dbo].[Pricing] ([ProductID], [PricingID], [COSBeginQty], [COSCOLCode], [COSTrade], [COSTypeOfPrice], [COSIsActive], [COSPrice], [COSPriceTAG], [RowIndex], [COSCreateDate], [COSLastModifiedDate]) VALUES (N'e2216b52-66a9-4c29-a8ec-83c6ae03cd18', N'a8c27e9a-120c-47f2-bdd9-3e9e934ca237', 12, N'TEM', N'T1', N'B ', 1, CAST(7234.000 AS Decimal(12, 3)), N'555c0f25-6af9-4114-8f11-096f0e5c7bcd', 1, NULL, NULL) GO ALTER TABLE [dbo].[Pricing] ADD CONSTRAINT [DF_Pricing_ProductID] DEFAULT (newid()) FOR [ProductID] GO ALTER TABLE [dbo].[Pricing] ADD CONSTRAINT [DF_Pricing_PricingID] DEFAULT (newid()) FOR [PricingID] GO ALTER TABLE [dbo].[Pricing] ADD CONSTRAINT [DF_Pricing_COSPriceTAG] DEFAULT (newid()) FOR [COSPriceTAG] GO 

SOLUTION TO THE PROBLEM AS BELOW THANKS THANKS

 select *, row_number() over(partition by ProductID order by ProductID) rn from dbo.pricing; DECLARE @colsUnpivot AS NVARCHAR(MAX), @query AS NVARCHAR(MAX), @colsPivot as NVARCHAR(MAX) select @colsUnpivot = stuff((select ','+quotename(C.name) from sys.columns as C where C.object_id = object_id('Pricing') and C.name LIKE '%COS%' and C.name Not in ('COSCreateDate', 'COSLastModifiedDate') for xml path('')), 1, 1, '') select @colsPivot = STUFF((SELECT ',' + quotename(c.name + cast(t.rn as varchar(10))) from ( select row_number() over(partition by productid order by productid) rn from Pricing ) t cross apply sys.columns as C where C.object_id = object_id('Pricing') and C.name LIKE '%COS%' and C.name Not in ('COSCreateDate', 'COSLastModifiedDate') group by c.name, t.rn order by t.rn FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query = 'select * from ( select ProductID, col + cast(rn as varchar(10)) new_col, val from ( select ProductID, PricingID, cast(COSBeginQty as varchar(50)) COSBeginQty, cast(COSCOLCode as varchar(50)) COSCOLCode, cast(COSTrade as varchar(50)) COSTrade, cast(COSTypeOfPrice as varchar(50)) COSTypeOfPrice, cast(COSIsActive as varchar(50)) COSIsActive, cast(COSPrice as varchar(50)) COSPrice, cast(COSPriceTAG as varchar(50)) COSPriceTAG, RowIndex, row_number() over(partition by productid order by productid) rn from Pricing ) x unpivot ( val for col in ('+ @colsunpivot +') ) u ) x1 pivot ( max(val) for new_col in ('+ @colspivot +') ) p' exec(@query) 

Answer 1

Without seeing your full table structure or sample data, it looks like you can do something similar using dynamic SQL. The UNPIVOT and PIVOT values ​​are used to obtain the results:

 DECLARE @colsUnpivot AS NVARCHAR(MAX), @query AS NVARCHAR(MAX), @colsPivot as NVARCHAR(MAX) 

The first part of @colsUnpivot to get the list of columns you need for UNPIVOT to flip again. You need UNPIVOT because you want your result to include several versions of each of these columns - CosBeginQty1, CosBeginQty2, CosBeginQty3

 select @colsUnpivot = stuff((select ','+quotename(C.name) from sys.columns as C where C.object_id = object_id('Pricing') and C.name LIKE '%COS%' for xml path('')), 1, 1, '') 

The code to get @colsPivot gets the final columns. These values ​​( CosBeginQty1, CosBeginQty2, CosBeginQty3 ) are retrieved by applying row_number to each row in the table.

 select @colsPivot = STUFF((SELECT ',' + quotename(c.name + cast(t.rn as varchar(10))) from ( select row_number() over(partition by productid order by productid) rn from Pricing ) t cross apply sys.columns as C where C.object_id = object_id('Pricing') and C.name LIKE '%COS%' group by c.name, t.rn order by t.rn FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') 

This final part generates a dynamic sql statement to execute. It uses the values ​​of @colsUnpivot and @colsPivot to form the final sql statement for the query.

 set @query = 'select * from ( select ProductID, PricingID, RowIndex, col + cast(rn as varchar(10)) new_col, val from ( select ProductID, PricingID, COSBeginQty, COSCOLCode, COSTrade, COSTypeOfPrice, COSIsActive, COSPrice, COSPriceTAG, RowIndex, row_number() over(partition by productid order by productid) rn from Pricing ) x unpivot ( val for col in ('+ @colsunpivot +') ) u ) x1 pivot ( max(val) for new_col in ('+ @colspivot +') ) p' exec(@query) 

This solution associates row_number() with each of the values, then UNPIVOT columns, and finally PIVOT them with row_number() added to each column name.

A PIVOT and UNPIVOT can be performed statically, but since you will have an unknown number of PIVOT values, you must use the dynamic version. If you used the static version, it would look something like this:

 select * from ( select ProductID, PricingID, RowIndex, col + cast(rn as varchar(10)) new_col, val from ( select ProductID, PricingID, COSBeginQty, COSCOLCode, COSTrade, COSTypeOfPrice, COSIsActive, COSPrice, COSPriceTAG, RowIndex, row_number() over(partition by productid order by productid) rn from Pricing ) x unpivot ( val for col in (COSBeginQty, COSCOLCode, COSTrade, COSTypeOfPrice COSIsActive, COSPrice, COSPriceTAG) ) u ) x1 pivot ( max(val) for new_col in ([COSBeginQty1], [COSCOLCode1], [COSTrade1], [COSTypeOfPrice1], [COSIsActive1], [COSPrice1], [COSPriceTAG1], [COSBeginQty2], [COSCOLCode2], [COSTrade2], [COSTypeOfPrice2], [COSIsActive2], [COSPrice2], [COSPriceTAG2]) ) p 

Change # 2, applying your sample data, the script will look like this:

 DECLARE @colsUnpivot AS NVARCHAR(MAX), @query AS NVARCHAR(MAX), @colsPivot as NVARCHAR(MAX) select @colsUnpivot = stuff((select ','+quotename(C.name) from sys.columns as C where C.object_id = object_id('Pricing') and C.name LIKE '%COS%' and C.name Not in ('COSCreateDate', 'COSLastModifiedDate') for xml path('')), 1, 1, '') select @colsPivot = STUFF((SELECT ',' + quotename(c.name + cast(t.rn as varchar(10))) from ( select row_number() over(partition by productid order by productid) rn from Pricing ) t cross apply sys.columns as C where C.object_id = object_id('Pricing') and C.name LIKE '%COS%' and C.name Not in ('COSCreateDate', 'COSLastModifiedDate') group by c.name, t.rn order by t.rn FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query = 'select * from ( select ProductID, PricingID, RowIndex, col + cast(rn as varchar(10)) new_col, val from ( select ProductID, PricingID, cast(COSBeginQty as varchar(50)) COSBeginQty, cast(COSCOLCode as varchar(50)) COSCOLCode, cast(COSTrade as varchar(50)) COSTrade, cast(COSTypeOfPrice as varchar(50)) COSTypeOfPrice, cast(COSIsActive as varchar(50)) COSIsActive, cast(COSPrice as varchar(50)) COSPrice, cast(COSPriceTAG as varchar(50)) COSPriceTAG, RowIndex, row_number() over(partition by productid order by productid) rn from Pricing ) x unpivot ( val for col in ('+ @colsunpivot +') ) u ) x1 pivot ( max(val) for new_col in ('+ @colspivot +') ) p' exec(@query) 

See SQL Fiddle with Demo

Answer 2

If you can use dynamic sql, then this can do for you.

 DECLARE @OUTPUT NVARCHAR(MAX) SET @OUTPUT = "XXXXX" SELECT @OUTPUT = @OUTPUT + "," + PricingID + COSBeginQty + COSCOLCode + COSTrade + COSTypeOfPrice + COSIsActive + COSPrice + COSPriceTAG + RowIndex FROM Pricing Where ProductID = XXXXX