如何将数据一列分多列
问题描述
iten_no 分列显示
A列 item_no中该单号的 01开头的
B列 item_no中该单号的 02开头的
C列 item_no中该单号的 03开头的
D列 item_no中该单号的 04开头的
如同一字段有多个值，以逗号隔开
显示效果如下
---创建表
CREATE TABLE [tb] (
[voucher] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[item_no] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
--插入数据
insert into tb values ('S01','011111211')
insert into tb values ('S01','021113331')
insert into tb values ('S01','03122111')
insert into tb values ('S01','0411121')
insert into tb values ('S01','0111211')
insert into tb values ('S02','0111123')
insert into tb values ('S02','031112')
insert into tb values ('S03','011112')
insert into tb values ('S03','011113')
insert into tb values ('S03','031113')
---创建表
CREATE TABLE [tb] (
[voucher] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[item_no] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
--插入数据
insert into tb values ('S01','011111211')
insert into tb values ('S01','021113331')
insert into tb values ('S01','03122111')
insert into tb values ('S01','0411121')
insert into tb values ('S01','0111211')
insert into tb values ('S02','0111123')
insert into tb values ('S02','031112')
insert into tb values ('S03','011112')
insert into tb values ('S03','011113')
insert into tb values ('S03','031113')
;WITH cte AS
(
SELECT *, ch=(CASE LEFT(item_no,2) WHEN '01' THEN 'A' WHEN '02' THEN 'B' WHEN '03' THEN 'C' WHEN '04' THEN 'D' ELSE '' END) FROM tb
)
SELECT VOUCHER, A=ISNULL(a,''), B=ISNULL(b, ''), C=ISNULL(C,''),D=ISNULL(d,'') FROM
(
SELECT voucher, ch, item_no=STUFF((SELECT ','+item_no FROM cte B WHERE b.voucher = a.voucher AND b.ch=a.ch FOR XML PATH('')),1,1,'')
FROM cte A
GROUP BY a.voucher, a.ch
) A
PIVOT
(MAX(item_no) FOR ch IN([A],[B],[C],[D])) b
/*
VOUCHERABCD
S01011111211,0111211021113331031221110411121
S020111123031112
S03011112,011113031113
*/
--sql2000中只能用自定义的函数
IF OBJECT_ID('dbo.fn_merger', 'FN') IS NOT NULL
DROP FUNCTION dbo.fn_merger
GO
CREATE FUNCTION fn_merger(@voucher VARCHAR(10),@item_no CHAR(2))
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @rv NVARCHAR(MAX)
SET @rv = N''
SELECT @rv = @rv + ',' + item_no FROM dbo.tb
WHERE voucher = @voucher AND LEFT(item_no, 2)=@item_no
RETURN STUFF(@rv,1,1,'')
END
GO
SELECT voucher,
MAX(case ch when '01' then item_no else '' end ) [A],
MAX(case ch when '02' then item_no else '' end ) [B],
MAX(case ch when '03' then item_no else '' end ) [C],
MAX(case ch when '04' then item_no else '' end ) [D]
FROM
(
select voucher, ch=LEFT(item_no,2), item_no=dbo.fn_merger(a.voucher, LEFT(a.item_no,2))
FROM TB a
GROUP BY VOUCHER, LEFT(item_no,2)
) t
GROUP BY voucher
/*
voucherABCD
S01011111211,0111211021113331031221110411121
S020111123031112
S03011112,011113031113
*/
