【提问】一个行转列问题，有点难度
人员信息表：A
人员Id 人员名称
1 李四
2 张三
......................
人员拥有卡片表:B
人员Id 卡片Id 卡片号码
1 001 1234
1 002 12345
1 003 123456
2 004 2345
2 005 23456
..........................
需要以下结果：
人员Id 卡片1 卡片2 卡片3 卡片4 .... 卡片N
1 1234 12345 123456 null null ...
2 2345 23456 null null null ..
..................................................
----------------------------------------------------------------[b]
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-12-16 11:11:07
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
--Dec 28 2012 20:23:12
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([人员Id] int,[人员名称] varchar(4))
insert [A]
select 1,'李四' union all
select 2,'张三'
--> 测试数据：
if object_id('[B]') is not null drop table [B]
go
create table [B]([人员Id] int,[卡片Id] varchar(3),[卡片号码] int)
insert [B]
select 1,'001',1234 union all
select 1,'002',12345 union all
select 1,'003',123456 union all
select 2,'004',2345 union all
select 2,'005',23456
--------------开始查询--------------------------
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename('卡片'+CONVERT(VARCHAR(2),CONVERT(INT,[卡片Id])))+'=max(case when [卡片Id]='+quotename([卡片Id],'''')+' then [卡片号码] else NULL end)'
from (select a.[人员Id],a.[人员名称],b.[卡片Id],b.[卡片号码] from [A] LEFT JOIN b ON a.[人员Id]=b.[人员Id]) a group by [卡片Id]
exec('select [人员Id]'+@s+' from (select a.[人员Id],a.[人员名称],b.[卡片Id],b.[卡片号码] from [A] LEFT JOIN b ON a.[人员Id]=b.[人员Id]) a group by [人员Id]')
----------------结果----------------------------
/*
人员Id 卡片1 卡片2 卡片3 卡片4 卡片5
----------- ----------- ----------- ----------- ----------- -----------
1 1234 12345 123456 NULL NULL
2 NULL NULL NULL 2345 23456
*/
create table B(人员Id int, 卡片Id varchar(10), 卡片号码 int)
insert into b
select 1 ,'001', 1234 union all
select 1 ,'002', 12345 union all
select 1 ,'003', 123456 union all
select 2 ,'004', 2345 union all
select 2 ,'005', 23456
go
declare @sql nvarchar(max);
set @sql = '';
;with t
as
(
select *,
ROW_NUMBER() over(partition by 人员Id order by 卡片Id) as rownum
from B
)
select
@sql = @sql + ',max(case when rownum = '+cast(rownum as varchar)+' then 卡片号码 else null end) as 卡片'+
cast(rownum as varchar)
from t
group by rownum
select @sql = 'select 人员Id' + @sql +
' from (select *,
ROW_NUMBER() over(partition by 人员Id order by 卡片Id) as rownum
from B)t' +
' group by 人员Id'
--select @sql
exec(@sql)
/*
人员Id卡片1卡片2卡片3
1123412345123456
2234523456NULL
*/
