SQL turning values returned in 11 rows into 89 tot

I need some help with turning rows of data into columns.

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

Normally, I would write a select statement like the one below to return all the pricing rows (about 11) that pertain to a particular ProductID

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

Each of my returned rows has about What I would like is 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 I need from all the 11 rows, there 89 total) columns

Here is a look at the script fro 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

THE SOLUTION TO THE PROBLEM IS AS BELOW THANKS TO BLUEFEET

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)

标签： sql sql-server pivot

1条回答

叛逆

2楼-- · 2019-06-10 19:28

Without seeing your full table structure or sample data it looks like you can do something like this, using dynamic SQL. This uses both UNPIVOT and PIVOT to get the results:

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

The first piece @colsUnpivot which gets the list of the columns that you need to UNPIVOT to then re-pivot. You need to UNPIVOT because you want your result to include multiple 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 the @colsPivot is getting the final columns. These values (CosBeginQty1, CosBeginQty2, CosBeginQty3) are retrieved by applying the row_number to each of the rows 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 the dynamic sql statement to execute. It uses both the @colsUnpivot and @colsPivot values to form the final sql statement to 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 a row_number() to each of the values, then UNPIVOTs the columns and finally PIVOTs them with the row_number() added to each column name.

A PIVOT and UNPIVOT can be performed statically but since you will have a unknown number of values to PIVOT then you should use a dynamic version. If you were using a static version then 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

Edit #2, applying your sample data, the script would 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

0人赞添加讨论(0) 举报

SQL turning values returned in 11 rows into 89 tot

采纳回答

编辑标签

举报内容

检举类型

检举原因

检举说明(必填)

打开微信“扫一扫”，打开网页后点击屏幕右上角分享按钮

付费偷看金额在0.1-10元之间