将多列行值转换为多列 [英] Convert multiple columns row values into multiple columns
问题描述
我已经尝试了围绕 Pivot、Unpivot 交叉表的所有可能性 - 随便你说.
I've tried all possibilities around Pivot, Unpivot crosstab - you name it.
困难在于我已经为此过度工作了.我希望这将是一个简单的解决方案，只是我看不到它.
Struggle is that i've over worked myself with this. I am hoping it wi going to be a simple solution just that I cant see it.
很抱歉没有在 DDL 中提供.
I'm so sorry for not providing in DDL.
但基本上我有一张看起来像这样的桌子.
But essentially I have a table that looks like this.
DeptName Metric1_Name Metric1_Value Metric2_Name Metric2_Value Metric3_Name Metric3_Value Metric4_Name Metric4_Value
ABC Sales Per Hour 200 Wins Per Hour 10 Leads per Hour 2 Losses per Hour 1
ABC Sales per Minute 20 Wins per Minute 1 Leads per minute 1 Losses per Minute 1
XYZ Sales Per Hour 5000 Wins Per Hour 300 Leads per Hour 20 Losses per Hour 10
XYZ Sales per Minute 2000 Wins per Minute 100 Leads per minute 10 Losses per Minute 10
我希望得到这样的结果
DeptName Sales per Hour Sales per minute Wins Per Hour Wins per Minute Leads per Hour Leads per minute Losses per Hour Losses per Minute
ABC 200 20 10 1 2 1 1 1
XYZ 5000 2000 300 100 20 10 10 10
我使用的是 SQL-Server 2012.但我想解决方案可以在 2008 R2 上运行
I'm using SQL-Server 2012. But i guess solutons can work on 2008 R2
捕获 - 1)名为 Metric1_name 等的列数未知.有人可以添加metric_36，我13天都不知道.
Catches - 1)the number of columns named Metric1_name etc are unknown. Someone can add metric_36 and I will not know it for 13 days.
2) 有超过 1 个字段，例如 DeptName，我认为可以在此示例中省略.一旦有人给我指示如何解决它，我会将它们添加进来.
2) There are more than 1 field like DeptName which i believe is ok to leave out for this example. Once someone gives me direction how to solve for it I'll add them in.
非常感谢您抽出时间帮助我.
I really appreciate you taking time to help me out.
干杯
推荐答案
首先，我强烈建议您重新设计当前的结构.您当前的结构未规范化，维护起来非常困难，尤其是如果您允许用户向表中添加新列.在我解释如何使用您当前的结构获得结果之前，我将演示如果您重新设计表格会更容易.
First things first, I highly suggest that you redesign your current structure. Your current structure is not normalized and will be incredibly difficult to maintain especially if you allow users to add new columns to your table. Before I explain how to get the result with your current structure, I will demonstrate how much easier this would be if you redesign your tables.
新表格设计:这是关于如何将表格重写为更灵活的工作模型的建议.如果您有以下表格和示例数据:
New Table Design: This is a suggestion on how you can rewrite your table into a more flexible working model. If you have the following tables and sample data:
-- contains the names of each metric you need to track
CREATE TABLE metric
(
[id] int,
[name] varchar(17)
);
INSERT INTO metric ([id], [name])
VALUES (1, 'Sales per Hour'), (2, 'Sales per Minute'),
(3, 'Wins per Hour'), (4, 'Wins per Minute'),
(5, 'Leads per Hour'), (6, 'Leads per Minute'),
(7, 'Losses per Hour'), (8, 'Losses per Minute');
-- contains the details of your departments
CREATE TABLE Departments
(
[id] int,
[name] varchar(3)
);
INSERT INTO Departments ([id], [name])
VALUES (1, 'ABC'), (2, 'XYZ');
-- associates the dept to each metric and the value
CREATE TABLE details
(
[deptid] int,
[metricid] int,
[value] int
);
INSERT INTO details ([deptid], [metricid], [value])
VALUES
(1, 1, 200), (1, 2, 20), (1, 3, 10),
(1, 4, 1), (1, 5, 2), (1, 6, 1),
(1, 7, 1), (1, 8, 1), (2, 1, 5000),
(2, 2, 2000), (2, 3, 300), (2, 4, 100),
(2, 5, 20), (2, 6, 10), (2, 7, 10),
(2, 8, 10);
这种设计更加灵活，因为您可以轻松添加要跟踪的新指标，而无需向表中添加新列.这甚至可以扩展为为捕获值的每一天添加一个日期/时间列.您可以使用以下方式轻松加入他们:
This design is more flexible because you can easily add new metrics to track without having to add a new column to a table. this can even be expanded to add a date/time column for each day the values are captured. You can easily join them using:
select d.name deptname, m.name, dt.value
from departments d
inner join details dt
on d.id = dt.deptid
inner join metric m
on dt.metricid = m.id;
请参阅SQL Fiddle with Demo.这将为您提供每个部门的所有指标以及相关值，然后可以使用数据透视将其转换为列:
See SQL Fiddle with Demo. This will give you all of the metrics for each department and the associated value which can then be converted to columns using pivot:
select deptname,
[Sales per hour], [Sales per minute],
[Wins per hour], [Wins per minute],
[Leads per hour], [Leads per minute],
[Losses per hour], [Losses per minute]
from
(
select d.name deptname, m.name, dt.value
from departments d
inner join details dt
on d.id = dt.deptid
inner join metric m
on dt.metricid = m.id
) src
pivot
(
max(value)
for name in ([Sales per hour], [Sales per minute],
[Wins per hour], [Wins per minute],
[Leads per hour], [Leads per minute],
[Losses per hour], [Losses per minute])
) piv;
请参阅SQL Fiddle with Demo.如果您有未知的指标类型，上述查询可以轻松转换为动态 SQL.
See SQL Fiddle with Demo. The above query could easily be converted to dynamic SQL if you have unknown metrics types.
使用现有表:您可以通过先取消透视列然后应用 PIVOT 函数来获得结果.我建议使用 CROSS APPLY 对数据进行反透视，以便您可以将多列成对地转换为行.使用 CROSS APPLY 进行反透视的语法是:
With Existing Table: You can get the result by first unpivoting your columns and then applying the PIVOT function. I would suggest using CROSS APPLY to unpivot the data so you can convert the multiple columns into rows in pairs. The syntax to unpivot using CROSS APPLY is:
select deptname, name, value
from yourtable
cross apply
(
values
(Metric1_Name, Metric1_Value),
(Metric2_Name, Metric2_Value),
(Metric3_Name, Metric3_Value),
(Metric4_Name, Metric4_Value)
) c (name, value)
请参阅SQL Fiddle with Demo.这会将您的数据转换为以下格式:
See SQL Fiddle with Demo. This gets your data into the format:
| DEPTNAME | NAME | VALUE |
|----------|-------------------|-------|
| ABC | Sales Per Hour | 200 |
| ABC | Wins Per Hour | 10 |
| ABC | Leads per Hour | 2 |
| ABC | Losses per Hour | 1 |
| ABC | Sales per Minute | 20 |
| ABC | Wins per Minute | 1 |
| ABC | Leads per minute | 1 |
| ABC | Losses per Minute | 1 |
一旦数据采用这种格式，您就可以轻松应用 PIVOT 功能.如果您的值数量有限，以下将起作用:
Once the data is in this format, you can easily apply the PIVOT function. The following will work if you have a limited number of values:
select deptname,
[Sales per hour], [Sales per minute],
[Wins per hour], [Wins per minute],
[Leads per hour], [Leads per minute],
[Losses per hour], [Losses per minute]
from
(
select deptname, name, value
from yourtable
cross apply
(
values
(Metric1_Name, Metric1_Value),
(Metric2_Name, Metric2_Value),
(Metric3_Name, Metric3_Value),
(Metric4_Name, Metric4_Value)
) c (name, value)
) d
pivot
(
max(value)
for name in ([Sales per hour], [Sales per minute],
[Wins per hour], [Wins per minute],
[Leads per hour], [Leads per minute],
[Losses per hour], [Losses per minute])
) piv
order by deptname;
由于您当前的表结构，如果您有未知值，这会变得更加复杂，但以下动态 SQL 脚本应该会为您提供所需的结果:
Because of your current table structure this becomes much more complicated if you have unknown values but the following dynamic SQL script should get you the result that you need:
DECLARE @colsUnpivotList AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@colsPivot as NVARCHAR(MAX),
@q nvarchar(max)
declare @temp table
(
name varchar(50),
pos int
) ;
-- create the list of columns for the cross apply
select @colsUnpivotList
= stuff((select ', ('+quotename('Metric'+CAST(seq as varchar(2))+nm)
+ ', '+quotename('Metric'+CAST(seq as varchar(2))+vl) +')'
from
(
select distinct substring(C.COLUMN_NAME, 7, CHARINDEX('_', c.column_name)-7) seq
from INFORMATION_SCHEMA.columns as C
where C.TABLE_NAME = 'yourtable'
and C.COLUMN_NAME not in ('DeptName')
) s
cross join
(
select '_Name', '_Value'
) c (nm, vl)
for xml path('')), 1, 1, '')
-- create a sql string to get the list of values to be pivoted
select @q = stuff((select 'union select '+c.COLUMN_NAME + ' nm, '+ cast(c.ordinal_position as varchar(10))+' pos from yourtable '
from INFORMATION_SCHEMA.columns as C
where C.TABLE_NAME = 'yourtable'
and C.COLUMN_NAME not in ('DeptName')
and C.COLUMN_NAME like ('%_Name')
for xml path('')), 1, 6, '')
insert into @temp execute(@q );
-- use the @temp table to get the list of values to pivot
select @colsPivot = STUFF((SELECT ',' + quotename(name)
from @temp
group by name, pos
order by pos
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT deptname, ' + @colsPivot + '
from
(
select deptname, name, value
from yourtable
cross apply
(
values
'+@colsUnpivotList +'
) c (name, value)
) x
pivot
(
max(value)
for name in (' + @colsPivot + ')
) p '
execute sp_executesql @query;
请参阅SQL Fiddle with Demo.所有版本都得到结果:
See SQL Fiddle with Demo. All versions get the result:
| DEPTNAME | SALES PER HOUR | SALES PER MINUTE | WINS PER HOUR | WINS PER MINUTE | LEADS PER HOUR | LEADS PER MINUTE | LOSSES PER HOUR | LOSSES PER MINUTE |
|----------|----------------|------------------|---------------|-----------------|----------------|------------------|-----------------|-------------------|
| ABC | 200 | 20 | 10 | 1 | 2 | 1 | 1 | 1 |
| XYZ | 5000 | 2000 | 300 | 100 | 20 | 10 | 10 | 10 |
这篇关于将多列行值转换为多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
