SQL将11行中返回的值转换为89列 [英] SQL turning values returned in 11 rows into 89 total columns
问题描述
在将数据行转换为列时，我需要一些帮助.
I need some help with turning rows of data into columns.
我的每一行都由8列组成，我希望将所有这些列都包含在最终的列列表中.
Each of my rows consists of 8 columns, all of which I would like to be included in the final column list.
通常，我会像下面这样写一个select语句，以返回与特定ProductID
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 . . . .
. . (计算我需要的所有11行值，总共89行)
. . .(counting the values I need from all the 11 rows, there 89 total) columns
这是我的桌子上的脚本
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
该问题的解决方案仅次于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来执行类似的操作.这同时使用UNPIVOT和PIVOT来获取结果:
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)
第一部分@colsUnpivot，它获取您需要UNPIVOT然后重新旋转的列的列表.您需要UNPIVOT，因为您希望结果包含这些列中的每一个的多个版本-CosBeginQty1, CosBeginQty2, CosBeginQty3
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, '')
获取@colsPivot的代码正在获取最后一列.通过将row_number应用于表中的每一行来检索这些值(CosBeginQty1, CosBeginQty2, CosBeginQty3).
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,'')
这最后一部分将生成要执行的动态sql语句.它同时使用@colsUnpivot和@colsPivot值形成要查询的最终sql语句.
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)
此解决方案将row_number()与每个值相关联，然后UNPIVOT s列，最后PIVOT将它们与添加到每个列名的row_number()一起.
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和UNPIVOT可以静态执行，但是由于PIVOT的值数量未知，因此应该使用动态版本.如果您使用的是静态版本，则它将类似于以下内容:
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
编辑#2，应用示例数据，脚本如下所示:
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)
请参见带演示的SQL提琴
这篇关于SQL将11行中返回的值转换为89列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
