一个列转行的sql

[北冥小渔夫]

列转行，item和item_name 个数不定

CREATE TABLE #A(code VARCHAR(30),name VARCHAR(200),
item1 VARCHAR(30), item_name1 VARCHAR(100) ,
item2 VARCHAR(30), item_name2 VARCHAR(100))
INSERT INTO #A
SELECT '1001','A1001','W01','W011001','W03','W011003' UNION ALL
SELECT '1002','D1001','K01','K011001','P09','P011009' UNION ALL
SELECT '1003','G1001','M01','M011001','N03','N011003' 

列转行的结果：
code item item_name
1001 W01 W011001
1001 W03 W011003
1002 K01 K011001
1002 P09 P011009
1003 M01 M011001
1003 N03 N011003

[wwfxgm]

这个题目。比较有现实意义。同求解法。

[锟斤拷锟斤拷]

刚学会pivot和unpivot的用法，因为这个涉及到两个相关的列要同时转行，直接unpivot的话会出现十二行结果...（每个item各对应每个itemname一次） 所以我就想能不能干脆对每个需要转行的列分别转完之后join起来呢，写的比较复杂，抛砖引玉。同求解法。

select a.code,a.i1 item,b.i1 itemname from
(select
code,i1,ROW_NUMBER() over(order by code) xx
from
(
select *
from #A
)lista
UNPIVOT(
i1
For ITEMS IN
([item1],[item2])
)AS T1)a
left join(
select
code,i1,ROW_NUMBER() over(order by code) xx
from
(
select *
from #A 
)lista
UNPIVOT(
i1
For ITEMNAMES IN
([item_name1],[item_name2])
)AS T2)b on a.xx = b.xx

[雨夹雪]

--列数不固定,但是item和item_name后面跟的数字是一套一套的

CREATE TABLE #A(code VARCHAR(30),name VARCHAR(200),
item1 VARCHAR(30), item_name1 VARCHAR(100) ,
item2 VARCHAR(30), item_name2 VARCHAR(100) ,
item3 VARCHAR(30), item_name3 VARCHAR(100) 
)
INSERT INTO #A(code,name,item1,item_name1,item2,item_name2)
SELECT '1001','A1001','W01','W011001','W03','W011003' UNION ALL
SELECT '1002','D1001','K01','K011001','P09','P011009' UNION ALL
SELECT '1003','G1001','M01','M011001','N03','N011003' 

INSERT INTO #A(code,name,item3,item_name3)
VALUES('1004','xxxx','yy','yy001')

INSERT INTO #A(code,name,item1,item_name1,item2,item_name2,item3,item_name3)
VALUES('1005','11',1,2,3,4,5,6)


DECLARE @sql VARCHAR(max)=''
DECLARE @where VARCHAR(max)=''
DECLARE @filed VARCHAR(max)=''

--动态拼接,先统一类型,然后列转行,在关联得到结果 
SELECT 
@filed=@filed+',CONVERT(VARCHAR(100),'+name+') AS '+name ,
@where=@where+','+name
FROM tempdb.sys.columns WHERE object_id=OBJECT_ID('tempdb..#A') AND name LIKE 'item_%'

SET @sql='
WITH ct
AS
(
	SELECT *   FROM 
	(
	SELECT 
	code '+@filed+'
	FROM #A
	) A
	UNPIVOT
	(
		x FOR y IN ('+STUFF(@where,1,1,'')+')
	)p
)
SELECT a.code,a.x AS item,b.x AS item_name FROM ct a 
INNER JOIN ct b ON b.code = a.code AND STUFF(a.y,1,4,'''')=STUFF(b.y,1,9,'''') AND SUBSTRING(a.y,5,1)<>''_'''

EXEC (@sql)

DROP TABLE #A

[北冥小渔夫]

谢谢，