★行转列★

Question

itemname                    profilecode                               itemvalue                             UsedCarValuePresentation 5626abd7-1457-4a63-b6b1-b45cb32074d5        0x32
LanguageType          5626ABD7-1457-4A63-B6B1-B45CB32074D5        0x31
ImplementationStyle 5626ABD7-1457-4A63-B6B1-B45CB32074D5       0x31怎么能把上面的数据转换成profilecode                           UsedCarValuePresentation   LanguageType  ImplementationStyle5626abd7-1457-4a63-b6b1-b45cb32074d5     0x32                         0x31         0x31有种方法是多次自连接来实现,有没有高效的方法.只需要读一次表就可以完成.

Answer 1

--> 测试数据: [tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb] (itemname varchar(24),profilecode varchar(36),itemvalue varchar(4))
insert into [tb]
select 'UsedCarValuePresentation','5626abd7-1457-4a63-b6b1-b45cb32074d5','0x32' union all
select 'LanguageType','5626ABD7-1457-4A63-B6B1-B45CB32074D5','0x31' union all
select 'ImplementationStyle','5626ABD7-1457-4A63-B6B1-B45CB32074D5','0x31'
godeclare @sql varchar(8000)
set @sql='select profilecode'
select @sql=@sql+',['+itemname+']=max(case itemname when '''+itemname+''' then itemvalue else null end)'
from [tb]
set @sql=@sql+' from tb group by profilecode'
exec(@sql)--结果：
profilecode                          UsedCarValuePresentation LanguageType ImplementationStyle
------------------------------------ ------------------------ ------------ -------------------
5626abd7-1457-4a63-b6b1-b45cb32074d5 0x32                     0x31         0x31

Answer 2

请参考：create table tb(名称 varchar(10),数量  numeric(10),类型 varchar(5))
Insert into tb 
select 'L001','1','A'
union all select 'L001','2','B'
union all select 'L002','5','C'
union all select 'L003','6','D'
union all select 'L004','9','A'
union all select 'L004','5','D'select * from tbdeclare @sql varchar(1000)
set @sql=''
select @sql=@sql+',['+max(类型)+']=sum(case 类型 when '''+max(类型)+''' then 数量 else 0 end)'
from tb group by 类型 
print @sqlexec('select 名称'+@sql+' from  tb  group by 名称')
--结果
名称      A       B        C        D
---------------------------------------
L001 1 2 0 0
L002 0 0 5 0
L003 0 0 0 6
L004 9 0 0 5

Answer 3

以前保留的行转列的demo，请你自行参考。
SQL是一个，但是包含两种方式。/*
CREATE TABLE [Demo01_Row2Col_Score](
[Id] [int] IDENTITY(1,1) NOT NULL,
[cname] [nvarchar](32) NULL,
[sname] [nvarchar](32) NULL,
[result] [int] NULL
) ON [PRIMARY]INSERT INTO [Demo01_Row2Col_Score] ([cname],[sname],[result]) VALUES ('数学','张三',94)
INSERT INTO [Demo01_Row2Col_Score] ([cname],[sname],[result]) VALUES ('数学','李四',71)
INSERT INTO [Demo01_Row2Col_Score] ([cname],[sname],[result]) VALUES ('数学','王五',87)
INSERT INTO [Demo01_Row2Col_Score] ([cname],[sname],[result]) VALUES ('英语','张三',78)
INSERT INTO [Demo01_Row2Col_Score] ([cname],[sname],[result]) VALUES ('英语','李四',93)
INSERT INTO [Demo01_Row2Col_Score] ([cname],[sname],[result]) VALUES ('英语','王五',88)
INSERT INTO [Demo01_Row2Col_Score] ([cname],[sname],[result]) VALUES ('语文','张三',80)
INSERT INTO [Demo01_Row2Col_Score] ([cname],[sname],[result]) VALUES ('语文','李四',78)
INSERT INTO [Demo01_Row2Col_Score] ([cname],[sname],[result]) VALUES ('语文','王五',83)
*//* 行转列的示例 */select * from Demo01_Row2Col_Score
-------------------------------------------------------------
select 
sname
,数学=isnull(sum(case cname when '数学' then result end),0)
,英语=isnull(sum(case cname when '英语' then result end),0)
,语文=isnull(sum(case cname when '语文' then result end),0)
from Demo01_Row2Col_Score
group by sname
order by sname
-------------------------------------------------------------
declare @sql varchar(max)
set @sql='select sname'
select @sql=@sql+','+cname+'=isnull(sum(case cname when '''+cname+''' then result end),0)' 
from Demo01_Row2Col_Score group by cname
set @sql=@sql+' from Demo01_Row2Col_Score group by sname order by sname'
print @sql
exec (@sql)

Answer 4

动态SQL的方法不行,数据量大的话,@sql 很长很长,SQL执行错误.

Answer 5

use PracticeDB
go
if exists (select 1 from sysobjects where name='t')
drop table t
go
create table t (itemname varchar(50),profilecode varchar(100),itemvalue varchar(10))
insert into t
select 'UsedCarValuePresentation','5626abd7-1457-4a63-b6b1-b45cb32074d5','0x32' union all
select 'LanguageType','5626ABD7-1457-4A63-B6B1-B45CB32074D5','0x31' union all
select 'ImplementationStyle','5626ABD7-1457-4A63-B6B1-B45CB32074D5','0x31'select profilecode,max(case itemname when 'UsedCarValuePresentation' then itemvalue else null end ) UsedCarValuePresentation,
                   max(case itemname when 'LanguageType' then itemvalue else null end ) LanguageType,
                   max(case itemname when 'ImplementationStyle' then itemvalue else null end ) ImplementationStyle                  
from t
group by profilecode
profilecode UsedCarValuePresentation LanguageType ImplementationStyle
5626abd7-1457-4a63-b6b1-b45cb32074d5 0x32 0x31 0x31

Answer 6

--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([itemname] [nvarchar](30),[profilecode] [nvarchar](40),[itemvalue] [nvarchar](10))
INSERT INTO [tb]
SELECT 'UsedCarValuePresentation','5626abd7-1457-4a63-b6b1-b45cb32074d5','0x32' UNION ALL
SELECT 'LanguageType','5626ABD7-1457-4A63-B6B1-B45CB32074D5','0x31' UNION ALL
SELECT 'ImplementationStyle','5626ABD7-1457-4A63-B6B1-B45CB32074D5','0x31'-->SQL查询如下:
DECLARE @s VARCHAR(MAX)
SELECT @s=ISNULL(@s+',','')+QUOTENAME(itemname)
FROM tb
GROUP BY itemname
EXEC ('
SELECT * 
FROM [tb] 
PIVOT(MAX(itemvalue) FOR itemname IN ('+@s+')) b
')
/*
profilecode                              ImplementationStyle LanguageType UsedCarValuePresentation
---------------------------------------- ------------------- ------------ ------------------------
5626abd7-1457-4a63-b6b1-b45cb32074d5     0x31                0x31         0x32(1 行受影响)
*/