如何将行查成列

[qq_37753824]

tab1 字段1 字段2 字段3
A B C
D E F
G H I

tab2 字段1 字段2
A a1
A a2
A a3
D d1
D d2
D d3
有两张表。
两表字段1 对应
如何 查成
A B C a1 a2 a3
D E F d1 d2 d3
G H I null null null

[吉普赛的歌]

如果tab2还有： B b1 B b2 B b3 C c1 C c2 C c3 那结果就是： A B C a1 a2 a3 b1 b2 b3 c1 c2 c3 D E F d1 d2 d3 G H I null null null ???

[qq_37753824]

引用 1 楼 yenange 的回复:

如果tab2还有：
B b1
B b2
B b3
C c1
C c2
C c3

那结果就是：
A B C a1 a2 a3 b1 b2 b3 c1 c2 c3
D E F d1 d2 d3
G H I null null null

???

不， 就字段1对应，其他字段不关联

[二月十六]

--测试数据
if not object_id(N'tab1') is null
	drop table tab1
Go
Create table tab1([字段1] nvarchar(21),[字段2] nvarchar(21),[字段3] nvarchar(21))
Insert tab1
select N'A',N'B',N'C' union all
select N'D',N'E',N'F' union all
select N'G',N'H',N'I'
GO
if not object_id(N'tab2') is null
	drop table tab2
Go
Create table tab2([字段1] nvarchar(21),[字段2] nvarchar(22))
Insert tab2
select N'A',N'a1' union all
select N'A',N'a2' union all
select N'A',N'a3' union all
select N'D',N'd1' union all
select N'D',N'd2' union all
select N'D',N'd3'
Go
--测试数据结束
DECLARE @sql VARCHAR(MAX)
SET @sql = ';WITH cte AS (
Select *,ROW_NUMBER()OVER(PARTITION BY 字段1 ORDER BY 字段2) rn from tab2
)
select tab1.字段2,tab1.字段3,t.* from tab1 left join (select 字段1'
;WITH cte AS (
Select *,ROW_NUMBER()OVER(PARTITION BY 字段1 ORDER BY 字段2) rn from tab2
)

SELECT  @sql = @sql + ',max(case rn when ''' + RTRIM(a.rn)
        + ''' then 字段2 else '''' end)[' +  RTRIM(a.rn) + ']'
FROM    ( SELECT DISTINCT
                    rn
          FROM      cte
        ) a
SET @sql = @sql
    + ' from cte group by 字段1)t on tab1.字段1 = t.字段1'
EXEC(@sql)

[二月十六]

如果数据很整齐，也可以这样静态方法写

--测试数据
if not object_id(N'tab1') is null
	drop table tab1
Go
Create table tab1([字段1] nvarchar(21),[字段2] nvarchar(21),[字段3] nvarchar(21))
Insert tab1
select N'A',N'B',N'C' union all
select N'D',N'E',N'F' union all
select N'G',N'H',N'I'
GO
if not object_id(N'tab2') is null
	drop table tab2
Go
Create table tab2([字段1] nvarchar(21),[字段2] nvarchar(22))
Insert tab2
select N'A',N'a1' union all
select N'A',N'a2' union all
select N'A',N'a3' union all
select N'D',N'd1' union all
select N'D',N'd2' union all
select N'D',N'd3'
Go
--测试数据结束
;WITH cte AS (
SELECT *,ROW_NUMBER()OVER(PARTITION BY 字段1 ORDER BY 字段2) rn FROM tab2
)
SELECT
    tab1.*,
    t1.字段2,
    t2.字段2,
    t3.字段2
FROM
    tab1
    LEFT JOIN
        cte t1
            ON t1.字段1 = dbo.tab1.字段1
               AND t1.rn = 1
    LEFT JOIN
        cte t2
            ON t2.字段1 = dbo.tab1.字段1
               AND t2.rn = 2
    LEFT JOIN
        cte t3
            ON t3.字段1 = dbo.tab1.字段1
               AND t3.rn = 3;

[qq_37753824]

引用 4 楼 sinat_28984567 的回复:

如果数据很整齐，也可以这样静态方法写

--测试数据
if not object_id(N'tab1') is null
	drop table tab1
Go
Create table tab1([字段1] nvarchar(21),[字段2] nvarchar(21),[字段3] nvarchar(21))
Insert tab1
select N'A',N'B',N'C' union all
select N'D',N'E',N'F' union all
select N'G',N'H',N'I'
GO
if not object_id(N'tab2') is null
	drop table tab2
Go
Create table tab2([字段1] nvarchar(21),[字段2] nvarchar(22))
Insert tab2
select N'A',N'a1' union all
select N'A',N'a2' union all
select N'A',N'a3' union all
select N'D',N'd1' union all
select N'D',N'd2' union all
select N'D',N'd3'
Go
--测试数据结束
;WITH cte AS (
SELECT *,ROW_NUMBER()OVER(PARTITION BY 字段1 ORDER BY 字段2) rn FROM tab2
)
SELECT
    tab1.*,
    t1.字段2,
    t2.字段2,
    t3.字段2
FROM
    tab1
    LEFT JOIN
        cte t1
            ON t1.字段1 = dbo.tab1.字段1
               AND t1.rn = 1
    LEFT JOIN
        cte t2
            ON t2.字段1 = dbo.tab1.字段1
               AND t2.rn = 2
    LEFT JOIN
        cte t3
            ON t3.字段1 = dbo.tab1.字段1
               AND t3.rn = 3;

谢谢