SQL-将列转置为行 [英] SQL - Transpose a Column to Row
问题描述
好的，我已经搜索了，但是找不到像我在这里尝试的那样具体的东西.我有两个不同的表，我需要从中获取信息.这是一个示例架构-与我正在使用的架构类似:
Ok, I've searched but can't find anything as specific as I'm trying to do here. I have two different tables that I need info from. This is a sample schema - similar to what I'm working from:
create table Nodes(
Caption varchar(max),
IP_Address varchar(max),
NodeID varchar(max)
);
insert into Nodes (Caption, IP_Address, NodeID)
values ('dev-srvr', '10.0.0.1', '29023');
insert into Nodes (Caption, IP_Address, NodeID)
values ('prod-srvr', '10.0.2.1', '29056');
insert into Nodes (Caption, IP_Address, NodeID)
values ('test-srvr', '10.1.1.1', '29087');
create table Volumes(
Caption varchar(max),
NodeID varchar(max)
);
insert into Volumes (NodeID, Caption)
values ('29023', '/');
insert into Volumes (NodeID, Caption)
values ('29023', '/boot');
insert into Volumes (NodeID, Caption)
values ('29023', '/dev/shm');
insert into Volumes (NodeID, Caption)
values ('29023', '/home');
insert into Volumes (NodeID, Caption)
values ('29056', '/');
insert into Volumes (NodeID, Caption)
values ('29056', '/var');
insert into Volumes (NodeID, Caption)
values ('29056', '/opt');
insert into Volumes (NodeID, Caption)
values ('29087', '/tmp');
我正在尝试编写一个查询(带有where子句...在最终版本上将具有多个过滤器)，该查询将返回Node.Caption，IP_Address和每个关联的Volumes.Caption(基于NodeID).每个节点ID的Volumes.Caption中的条目数是动态的，范围从1到大约60左右.我所知道的是这样写的:
I'm attempting to write a query (with a where clause...will have multiple a filters on final version) that will return Node.Caption, IP_Address, and every Volumes.Caption associated (based on NodeID). The number of entries in Volumes.Caption for each NodeID is dynamic and varies from 1 to about 60 or so. All I know how to write is this:
select Nodes.Caption, Nodes.IP_Address, Volumes.Caption as Volume
from Nodes with (nolock)
inner join Volumes
on Nodes.NodeID=Volumes.NodeID
where IP_Address like '10.0%'
哪个返回以下内容:
Caption | IP_Address | Volume
---------------------------------
dev-srvr | 10.0.0.1 | /
dev-srvr | 10.0.0.1 | /boot
dev-srvr | 10.0.0.1 | /dev/shm
dev-srvr | 10.0.0.1 | /home
prod-srvr | 10.0.0.1 | /var
prod-srvr | 10.0.0.1 | /opt
但是我需要的是每个NodeID有一个ROW，显示Node.Caption，IP_Address和所有匹配的Volumes(如果可能).像这样(最后一列的名称并不重要...可以是任何东西):
But what I need is a single ROW per NodeID showing Node.Caption, IP_Address and all matching Volumes if possible. Like this (the final column names are not important...can be anything):
Caption | IP_Address | Volume1 | Volume2 | Volume3 | Volume 4
----------------------------------------------------------------
dev-srvr | 10.0.0.1 | / | /boot | /dev/shm | /home
prod-srvr | 10.0.0.1 | /var | /opt
推荐答案
SO上有100个枢轴示例，因此我想展示一种可以做到这一点的方法，虽然不如枢轴好，但可以为您的实例服务.它可能不适合您的整个数据集，并且仅基于示例数据.
There's 100 pivot examples on SO so I wanted to show a way that you could do this, which isn't as good as pivot, but works for your instance. It may not fit your entire dataset and was based on your sample data only.
值得注意的是，您的测试数据并未提供您声称的结果.可能只是插入内容上的错字.
Of note, your test data doesn't provide the results you claim they do. Probably just a typo on the insert.
create table #Nodes(
Caption varchar(max),
IP_Address varchar(max),
NodeID varchar(max)
);
insert into #Nodes (Caption, IP_Address, NodeID)
values
('dev-srvr', '10.0.0.1', '29023'),
('prod-srvr', '10.0.2.1', '29056'),
('test-srvr', '10.1.1.1', '29087');
create table #Volumes(
Caption varchar(max),
NodeID varchar(max)
);
insert into #Volumes (NodeID, Caption)
values
('29023', '/'),
('29023', '/boot'),
('29023', '/dev/shm'),
('29023', '/home'),
('29056', '/'),
('29056', '/var'),
('29056', '/opt'),
('29087', '/tmp');
select
n.Caption,
n.IP_Address,
v.Caption as Volume
from #Nodes n
inner join #Volumes v
on n.NodeID=v.NodeID
where IP_Address like '10.0%'
;with cte as(
select
n.Caption,
n.IP_Address,
v.Caption as Volume,
ROW_NUMBER() over (partition by n.caption, IP_Address order by n.caption) as RN
from #Nodes n
inner join #Volumes v
on n.NodeID=v.NodeID
where IP_Address like '10.0%')
select
x.caption,
x.IP_Address,
max(Volume1) as Volume1,
max(Volume2) as Volume2,
max(Volume3) as Volume3,
max(Volume4) as Volume4
from(
select
Caption,
IP_Address,
case when RN = 1 then Volume end as Volume1,
case when RN = 2 then Volume end as Volume2,
case when RN = 3 then Volume end as Volume3,
case when RN = 4 then Volume end as Volume4
from cte) x
group by x.Caption, x.IP_Address
drop table #Nodes
drop table #Volumes
使用动态PIVOT
create table #Nodes(
Caption varchar(max),
IP_Address varchar(max),
NodeID varchar(max)
);
insert into #Nodes (Caption, IP_Address, NodeID)
values
('dev-srvr', '10.0.0.1', '29023'),
('prod-srvr', '10.0.2.1', '29056'),
('test-srvr', '10.1.1.1', '29087');
create table #Volumes(
Caption varchar(max),
NodeID varchar(max)
);
insert into #Volumes (NodeID, Caption)
values
('29023', '/'),
('29023', '/boot'),
('29023', '/dev/shm'),
('29023', '/home'),
('29056', '/'),
('29056', '/var'),
('29056', '/opt'),
('29087', '/tmp');
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
select
n.Caption,
n.IP_Address,
v.Caption as Volume,
'Volume' + cast(ROW_NUMBER() over (partition by n.caption, IP_Address order by n.caption) as varchar(16)) as Cname
--ROW_NUMBER() over (partition by n.caption, IP_Address order by n.caption) as RN
into #staging
from #Nodes n
inner join #Volumes v
on n.NodeID=v.NodeID
where IP_Address like '10.0%'
--Get distinct values of the PIVOT Column
SELECT @ColumnName= ISNULL(@ColumnName + ',','')
+ QUOTENAME(Cname)
FROM (SELECT DISTINCT Cname FROM #staging) AS Cname
--Prepare the PIVOT query using the dynamic
SET @DynamicPivotQuery =
N'SELECT Caption, IP_Address, ' + @ColumnName + '
FROM #staging
PIVOT(MAX(Volume)
FOR Cname IN (' + @ColumnName + ')) AS PVTTable'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery
drop table #Nodes
drop table #Volumes
drop table #staging
这篇关于SQL-将列转置为行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
