将列数据转换为行

问题描述

大家好，我有一张TableA表，看起来很像，



cus_id | code1 | code1_desc | code2 | code2_desc | code3 | code3_desc

1 c1 xxx c1 xxx c1 xxx

1 c2 yyy c2 yyy c2 yyy

1 c3 zzz c3 zzz c3 zzz

2 c1 xxx c1 xxx c1 xxx

2 c2 yyy c2 yyy c2 yyy

2 c3 zzz c3 zzz c3 zzz



但我想要像这样的桌子，



cus_id | code1 | code1_desc | code2 | code2_desc | code3 | code3_desc

1 c1 xxx c2 yyy c3 zzz

2 c1 xxx c2 yyy c3 zzz



请给一些Sugges.Thanks in Advance ..

Hi all,i have a table TableA which looks like,

cus_id | code1 | code1_desc | code2 | code2_desc | code3 | code3_desc
1 c1 xxx c1 xxx c1 xxx
1 c2 yyy c2 yyy c2 yyy
1 c3 zzz c3 zzz c3 zzz
2 c1 xxx c1 xxx c1 xxx
2 c2 yyy c2 yyy c2 yyy
2 c3 zzz c3 zzz c3 zzz

but i want table like,

cus_id | code1 | code1_desc | code2 | code2_desc | code3 | code3_desc
1 c1 xxx c2 yyy c3 zzz
2 c1 xxx c2 yyy c3 zzz

Please give some suggestions.Thanks in Advance..

推荐答案

SELECT t1.cus_id,t1.code1,t1.code1_desc,t2.code2,t2.code2_desc,t3.code3,t3.code3_desc
FROM TableA t1
INNER JOIN TableA t2 ON t1.cus_id=t2.cus_id AND t2.code1='c2'
INNER JOIN TableA t3 ON t2.cus_id=t3.cus_id AND t3.code1='c3'
WHERE t1.code1='c1'

示例：

Example:

DECLARE @tbl AS TABLE(cus_id INT,code1 VARCHAR(10),code1_desc VARCHAR(20),code2 VARCHAR(10),code2_desc VARCHAR(20),code3 VARCHAR(10),code3_desc VARCHAR(20))

INSERT INTO @tbl
SELECT 1 cus_id,'c1' code1,'xxx' code1_desc,'c1' code2,'xxx' code2_desc, 'c1' code3 ,'xxx' code3_desc
UNION ALL
SELECT 1, 'c2', 'yyy', 'c2', 'yyy', 'c2', 'yyy'
UNION ALL
SELECT 1, 'c3', 'zzz', 'c3', 'zzz', 'c3', 'zzz'
UNION ALL
SELECT 2, 'c1', 'xxx', 'c1', 'xxx', 'c1', 'xxx'
UNION ALL
SELECT 2, 'c2', 'yyy', 'c2', 'yyy', 'c2', 'yyy'
UNION ALL
SELECT 2, 'c3', 'zzz', 'c3', 'zzz', 'c3', 'zzz'

--select * from @tbl

SELECT t1.cus_id,t1.code1,t1.code1_desc,t2.code2,t2.code2_desc,t3.code3,t3.code3_desc
FROM @tbl t1
INNER JOIN @tbl t2 ON t1.cus_id=t2.cus_id and t2.code1='c2'
INNER JOIN @tbl t3 ON t2.cus_id=t3.cus_id and t3.code1='c3'
WHERE t1.code1='c1'

但我相信你的表结构有问题。请查看它。



希望，它有帮助：）

But I believe you table structure has a problem. Please review it.

Hope, it helps :)

这篇关于将列数据转换为行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！