【100分】困难的行列转换，总是吃不透。。。。

[丰云]

如上图，想实现这样的行列转换，看了很久的pivot语句的资料，还是写不出来，
请大侠们帮帮忙！！

[听雨停了]

WITH tab AS ( SELECT 'A' AS boss,'asd' AS emp UNION ALL SELECT 'A','eqwe' UNION ALL SELECT 'A','eqweq' UNION ALL SELECT 'A','ewqe' UNION ALL SELECT 'B','weq' UNION ALL SELECT 'B','sda' UNION ALL SELECT 'C','adsa' ) SELECT boss, MAX(CASE WHEN num = '1' THEN emp END)AS '1', MAX(CASE WHEN num = '2' THEN emp END)AS '2', MAX(CASE WHEN num = '3' THEN emp END)AS '3', MAX(CASE WHEN num = '4' THEN emp END)AS '4', MAX(CASE WHEN num = '5' THEN emp END)AS '5' FROM ( SELECT *,ROW_NUMBER() OVER ( PARTITION BY boss ORDER BY emp) AS num FROM tab ) t GROUP BY boss

是这样吗？

[丰云]

引用 1 楼 qq_37170555 的回复:

WITH tab AS ( 。。。 是这样吗？

我这个不是普通的透视转换，得到的列数，每行可能都不一样

[听雨停了]

引用 1 楼 qq_37170555 的回复:

WITH tab AS ( SELECT 'A' AS boss,'asd' AS emp UNION ALL SELECT 'A','eqwe' UNION ALL SELECT 'A','eqweq' UNION ALL SELECT 'A','ewqe' UNION ALL SELECT 'B','weq' UNION ALL SELECT 'B','sda' UNION ALL SELECT 'C','adsa' ) SELECT boss, MAX(CASE WHEN num = '1' THEN emp END)AS '1', MAX(CASE WHEN num = '2' THEN emp END)AS '2', MAX(CASE WHEN num = '3' THEN emp END)AS '3', MAX(CASE WHEN num = '4' THEN emp END)AS '4', MAX(CASE WHEN num = '5' THEN emp END)AS '5' FROM ( SELECT *,ROW_NUMBER() OVER ( PARTITION BY boss ORDER BY emp) AS num FROM tab ) t GROUP BY boss

是这样吗？

如果你不确定有多少列，就先查一下你那个表一个办事处最多对应了多少个社区，有多少个，就写多少个，当然你多写几个MAX(CASE WHEN num = '5' THEN emp END)AS '5'也没事

[二月十六]

这个形式不能用pivot（也可能是我理解的不到位），还少一列，想要用pivot，得是这样的数据

--测试数据
if not object_id(N'T') is null
	drop table T
Go
Create table T([a] nvarchar(26),[b] nvarchar(24),[type] INT)
Insert T
select N'大智街办事处',N'保成社区',1 union all
select N'大智街办事处',N'泰宁社区',2 union all
select N'一元街办事处',N'岳飞社区',1 union all
select N'一元街办事处',N'同兴社区',2 union all
select N'一元街办事处',N'三阳社区',3
Go
--测试数据结束
SELECT  *
FROM    T PIVOT
( MAX([b]) FOR [type] IN ( [1], [2], [3] ) )
AS s

[繁花尽流年]

你给原始数据按办事处分组根据社区排个序，每个办事处分1,2,3,4...的社区序号，再把社区序号作为列就能用pivot转出来了。

[丰云]

引用 4 楼 sinat_28984567 的回复:

这个形式不能用pivot（也可能是我理解的不到位），还少一列，想要用pivot，得是这样的数据

--测试数据
if not object_id(N'T') is null
	drop table T
Go
Create table T([a] nvarchar(26),[b] nvarchar(24),[type] INT)
Insert T
select N'大智街办事处',N'保成社区',1 union all
select N'大智街办事处',N'泰宁社区',2 union all
select N'一元街办事处',N'岳飞社区',1 union all
select N'一元街办事处',N'同兴社区',2 union all
select N'一元街办事处',N'三阳社区',3
Go
--测试数据结束
SELECT  *
FROM    T PIVOT
( MAX([b]) FOR [type] IN ( [1], [2], [3] ) )
AS s

是的，就是这样的，茅塞顿开，豁然开朗了 我知道怎么做了

[听雨停了]

引用 2 楼 foren_whb 的回复:

[quote=引用 1 楼 qq_37170555 的回复:] WITH tab AS ( 。。。 是这样吗？

我这个不是普通的透视转换，得到的列数，每行可能都不一样[/quote] with tab as 只是一个CTE罢了，转换的语句在下面 SELECT [办事处字段] , MAX(CASE WHEN num = '1' THEN emp END)AS '1', MAX(CASE WHEN num = '2' THEN emp END)AS '2', MAX(CASE WHEN num = '3' THEN emp END)AS '3', MAX(CASE WHEN num = '4' THEN emp END)AS '4', MAX(CASE WHEN num = '5' THEN emp END)AS '5' FROM ( SELECT *,ROW_NUMBER() OVER ( PARTITION BY [办事处字段] ORDER BY [社区字段]) AS num FROM [你的表名] ) t GROUP BY [办事处字段] 套用这个总会套，这个能看懂吧，大哥啊。我真是服了你了。 如果社区很多的话，你就多写几个MAX(CASE WHEN num = '5' THEN emp END)AS '5'，里面的数字一直往下加就可以

[二月十六]

这样动态添加一列可以：

--测试数据
if not object_id(N'T') is null
	drop table T
Go
Create table T([a] nvarchar(26),[b] nvarchar(24))
Insert T
select N'大智街办事处',N'保成社区' union all
select N'大智街办事处',N'泰宁社区' union all
select N'一元街办事处',N'岳飞社区' union all
select N'一元街办事处',N'同兴社区' union all
select N'一元街办事处',N'三阳社区'
Go
--测试数据结束
DECLARE @str NVARCHAR(max)=';WITH cte AS (
SELECT *,ROW_NUMBER()OVER(PARTITION BY a ORDER BY b) AS num FROM T
)
SELECT  *
FROM    cte PIVOT
( MAX([b]) FOR [num] IN ('
DECLARE @str1 NVARCHAR(max)=''
;WITH cte AS (
SELECT *,ROW_NUMBER()OVER(PARTITION BY a ORDER BY b) AS num FROM T
)
SELECT @str1=@str1+',['+RTRIM(num)+']' FROM (SELECT DISTINCT num FROM cte)t
SET @str=@str+ STUFF(@str1,1,1,'')+') ) AS s'
EXEC(@str)

[二月十六]

引用 9 楼 wwfxgm 的回复:

[quote=引用 8 楼 sinat_28984567 的回复:] 这样动态添加一列可以：

--测试数据
if not object_id(N'T') is null
	drop table T
Go
Create table T([a] nvarchar(26),[b] nvarchar(24))
Insert T
select N'大智街办事处',N'保成社区' union all
select N'大智街办事处',N'泰宁社区' union all
select N'一元街办事处',N'岳飞社区' union all
select N'一元街办事处',N'同兴社区' union all
select N'一元街办事处',N'三阳社区'
Go
--测试数据结束
DECLARE @str NVARCHAR(max)=';WITH cte AS (
SELECT *,ROW_NUMBER()OVER(PARTITION BY a ORDER BY b) AS num FROM T
)
SELECT  *
FROM    cte PIVOT
( MAX([b]) FOR [num] IN ('
DECLARE @str1 NVARCHAR(max)=''
;WITH cte AS (
SELECT *,ROW_NUMBER()OVER(PARTITION BY a ORDER BY b) AS num FROM T
)
SELECT @str1=@str1+',['+RTRIM(num)+']' FROM (SELECT DISTINCT num FROM cte)t
SET @str=@str+ STUFF(@str1,1,1,'')+') ) AS s'
EXEC(@str)

这个代码复制过去。老是报错。

Err] 42000 - [SQL Server]必须声明标量变量 "@str1"。
42000 - [SQL Server]“t”附近有语法错误。
42000 - [SQL Server]必须声明标量变量 "@str"。
42000 - [SQL Server]必须声明标量变量 "@str"。

[/quote] 用的是ssms吗？声明变量了吗？

[wwfxgm]

引用 8 楼 sinat_28984567 的回复:

这样动态添加一列可以：

--测试数据
if not object_id(N'T') is null
	drop table T
Go
Create table T([a] nvarchar(26),[b] nvarchar(24))
Insert T
select N'大智街办事处',N'保成社区' union all
select N'大智街办事处',N'泰宁社区' union all
select N'一元街办事处',N'岳飞社区' union all
select N'一元街办事处',N'同兴社区' union all
select N'一元街办事处',N'三阳社区'
Go
--测试数据结束
DECLARE @str NVARCHAR(max)=';WITH cte AS (
SELECT *,ROW_NUMBER()OVER(PARTITION BY a ORDER BY b) AS num FROM T
)
SELECT  *
FROM    cte PIVOT
( MAX([b]) FOR [num] IN ('
DECLARE @str1 NVARCHAR(max)=''
;WITH cte AS (
SELECT *,ROW_NUMBER()OVER(PARTITION BY a ORDER BY b) AS num FROM T
)
SELECT @str1=@str1+',['+RTRIM(num)+']' FROM (SELECT DISTINCT num FROM cte)t
SET @str=@str+ STUFF(@str1,1,1,'')+') ) AS s'
EXEC(@str)

这个代码复制过去。老是报错。

Err] 42000 - [SQL Server]必须声明标量变量 "@str1"。
42000 - [SQL Server]“t”附近有语法错误。
42000 - [SQL Server]必须声明标量变量 "@str"。
42000 - [SQL Server]必须声明标量变量 "@str"。