sql行转列

Question

SELECT tid,vendor,model FROM Terminals
结果：
   tid                                 vendor    model
5AC5535E-BA6E-41BC-9314-00575984812E   1001 K700c
FAECAF14-F379-48C3-8237-03472C3DD0BF   1016 KE608N
302FB413-DA69-4408-A60E-035205FF94A7   1016 KG90N
...select tid,aid,vvalue from TeminalAttribute
结果：
  tid        aiv      vvlaue
5AC5535E-BA6E-41BC-9314-00575984812E    1 银/蓝
FAECAF14-F379-48C3-8237-03472C3DD0BF    3 2580
302FB413-DA69-4408-A60E-035205FF94A7    4 2280
302FB413-DA69-4408-A60E-035205FF94A7    3       2980
...
select aid,aname from AttributeDefine
结果：
aid    aname
1      颜色         
3      建议市场价
4      最高限价
...
Terminals的tid与TeminalAttribute的tid关联
TeminalAttribute的aid与AttributeDefine的aid关联
最后结果: tid                                 vendor    model      颜色   建议市场价  最高限价
5AC5535E-BA6E-41BC-9314-00575984812E   1001 K700c     银/蓝
FAECAF14-F379-48C3-8237-03472C3DD0BF   1016 KE608N            2580
302FB413-DA69-4408-A60E-035205FF94A7   1016 KG90N             2280     2980

Answer 1

固定列数的行列转换 
如 
student subject grade 
--------- ---------- -------- 
student1 语文 80 
student1 数学 70 
student1 英语 60 
student2 语文 90 
student2 数学 80 
student2 英语 100 
…… 
转换为 
语文 数学 英语 
student1 80 70 60 
student2 90 80 100 
…… 
语句如下：select student, 
sum(decode(subject,'语文', grade,null)) "语文", 
sum(decode(subject,'数学', grade,null)) "数学", 
sum(decode(subject,'英语', grade,null)) "英语" 
from table 
group by student; 

Answer 2

if object_id('[terminals]') is not null drop table [terminals]
go
create table [terminals]([tid] uniqueidentifier,[vendor] int,[model] varchar(6))
insert [terminals]
select '5ac5535e-ba6e-41bc-9314-00575984812e',1001,'k700c' union all
select 'faecaf14-f379-48c3-8237-03472c3dd0bf',1016,'ke608n' union all
select '302fb413-da69-4408-a60e-035205ff94a7',1016,'kg90n'
if object_id('[teminalattribute]') is not null drop table [teminalattribute]
go
create table [teminalattribute]([tid] uniqueidentifier,[aid] int,[vvalue] varchar(5))
insert [teminalattribute]
select '5ac5535e-ba6e-41bc-9314-00575984812e',1,'银/蓝' union all
select 'faecaf14-f379-48c3-8237-03472c3dd0bf',3,'2580' union all
select '302fb413-da69-4408-a60e-035205ff94a7',4,'2280' union all
select '302fb413-da69-4408-a60e-035205ff94a7',3,'2980'
if object_id('[attributedefine]') is not null drop table [attributedefine]
go
create table [attributedefine]([aid] int,[aname] varchar(10))
insert [attributedefine]
select 1,'颜色' union all
select 3,'建议市场价' union all
select 4,'最高限价'select * from [terminals]
select * from [teminalattribute]
select * from [attributedefine]declare @sql nvarchar(max)
set @sql='select a.tid,a.vendor,a.model'
select @sql=@sql+',['+aname+']=max(case c.aname when '''+aname+''' then b.vvalue else null end)'
from (select distinct aid,aname from attributedefine ) t order by aid
--print @sql
exec(@sql+' from terminals a
join teminalattribute b on b.tid=a.tid
join attributedefine c on c.aid=b.aid
group by a.tid,a.vendor,a.model')/*
tid                                  vendor      model  颜色    建议市场价 最高限价
------------------------------------ ----------- ------ ----- ----- -----
5AC5535E-BA6E-41BC-9314-00575984812E 1001        k700c  银/蓝   NULL  NULL
FAECAF14-F379-48C3-8237-03472C3DD0BF 1016        ke608n NULL  2580  NULL
302FB413-DA69-4408-A60E-035205FF94A7 1016        kg90n  NULL  2980  2280(3 row(s) affected)
*/

Answer 3

DECLARE @Terminals TABLE(TID VARCHAR(50) ,VENDOR VARCHAR(10) ,MODER VARCHAR(10) )
INSERT @Terminals (TID,VENDOR,MODER)
SELECT '5AC5535E-BA6E-41BC-9314-00575984812E','1001','K700c' UNION ALL 
SELECT 'FAECAF14-F379-48C3-8237-03472C3DD0BF','1016','KE608N' UNION ALL
SELECT '302FB413-DA69-4408-A60E-035205FF94A7','1016','KG90N' 
--SELECT * FROM @Terminals
DECLARE @TeminalAttribute TABLE(TID VARCHAR(50),AID VARCHAR(10),VVALUE VARCHAR(10))
INSERT @TeminalAttribute (TID,AID,VVALUE)
SELECT '5AC5535E-BA6E-41BC-9314-00575984812E','1','银/蓝' UNION ALL 
SELECT 'FAECAF14-F379-48C3-8237-03472C3DD0BF','3','2580' UNION ALL 
SELECT '302FB413-DA69-4408-A60E-035205FF94A7',    '4', '2280' UNION ALL
SELECT '302FB413-DA69-4408-A60E-035205FF94A7',    '3',      '2980' 
--SELECT * FROM @TeminalAttributeDECLARE @AttributeDefine TABLE(AID VARCHAR(10),ANAME VARCHAR(10))
INSERT @AttributeDefine (AID,ANAME)
SELECT '1' ,     '颜色' UNION ALL         
SELECT '3',      '建议市场价' UNION ALL
SELECT '4'     , '最高限价' 
--SELECT * FROM @AttributeDefine
SELECT A.TID,A.VENDOR,A.MODER,B.颜色,MAX(B.建议市场价)建议市场价,MAX(B.最高限价)最高限价
FROM @Terminals A LEFT JOIN 
(SELECT DISTINCT A.TID,
(CASE WHEN A.AID='1' THEN VVALUE END)颜色,
(CASE WHEN A.AID='3' THEN VVALUE END)建议市场价,
(CASE WHEN A.AID='4' THEN VVALUE END)最高限价
FROM @TeminalAttribute A LEFT JOIN @AttributeDefine B ON (A.AID=B.AID)) B ON (A.TID=B.TID)
GROUP BY A.TID,A.VENDOR,A.MODER,B.颜色
ORDER BY A.VENDOR
/*TID                                     VENDOR  MODER   颜色    建议市场价 最高限价
5AC5535E-BA6E-41BC-9314-00575984812E 1001 K700c 银/蓝 NULL NULL
302FB413-DA69-4408-A60E-035205FF94A7 1016 KG90N NULL 2980 2280
FAECAF14-F379-48C3-8237-03472C3DD0BF 1016 KE608N NULL 2580 NULL
 */

Answer 4

3楼如果数据量不多的话可行，可我的数据量很大的，nvarchar变量存不了那么多东西4楼的写得貌似比较死有没有更好的办法呀。。

Answer 5

sql2005
动态
(n)varchar(max)
静态
pivot
sql2000
动态
(n)varchar ,长度不够 时，多个变量接，形如
exec(@s1 + @s2 + @s3 .... )
每个有8000长度，几个就解决问题了。静态
聚合(case when ... end)
动转静:
sql2005,结合sys.columns或者用xml类型得到要转的列列表，生成列列表字串，拼到 pivot 的参数中，执行动态语句。

Answer 6

declare @terminals table (tid varchar(100),verdor int,model varchar(10))
insert into @terminals select '5AC5535E-BA6E-41BC-9314-00575984812E',1001,'k700c'
            union all  select 'FAECAF14-F379-48C3-8237-03472C3DD0BF',1016,'ke608n'
            union all  select '302FB413-DA69-4408-A60E-035205FF94A7',1016,'kg90n'
declare @teminalattribute table (tid varchar(100),aid int,vvlaue varchar(10))
insert into @teminalattribute select '5AC5535E-BA6E-41BC-9314-00575984812E',1,'银/蓝'
                    union all select 'FAECAF14-F379-48C3-8237-03472C3DD0BF',3,'2580'
                    union all select '302FB413-DA69-4408-A60E-035205FF94A7',4,'2280'
                    union all select '302FB413-DA69-4408-A60E-035205FF94A7',3,'2980'
declare @attributedefine table (aid int,aname varchar(10))
insert into @attributedefine select 1,'颜色'
                  union all  select 3,'建议市场价'
                  union all  select 4,'最高限价'
--select b.vvlaue,c.aname from  @teminalattribute b
  --       join @attributedefine c on b.aid=c.aid
--select * from @attributedefine
------
select a.tid,a.verdor,a.model,max(b.颜色)颜色,max(b.建议市场价) 建议市场价,max(b.最高限价) 最高限价 from @terminals a join
(select tid,颜色= case when aid=1 then vvlaue else '' end,建议市场价= case when aid=3 then vvlaue else ''end,
             最高限价=case when aid=4 then vvlaue else ''end from @teminalattribute) b
on a.tid=b.tid
group by a.tid,a.verdor,a.model
order by 颜色 desc
5AC5535E-BA6E-41BC-9314-00575984812E 1001 k700c 银/蓝
FAECAF14-F379-48C3-8237-03472C3DD0BF 1016 ke608n 2580
302FB413-DA69-4408-A60E-035205FF94A7 1016 kg90n 2980 2280

Answer 7

--> --> (Roy)生成測試數據
 
if not object_id('Terminals') is null
drop table Terminals
Go
Create table Terminals([tid] uniqueidentifier,[vendor] int,[model] nvarchar(6))
Insert Terminals
select N'5AC5535E-BA6E-41BC-9314-00575984812E',1001,N'K700c' union all
select N'FAECAF14-F379-48C3-8237-03472C3DD0BF',1016,N'KE608N' union all
select N'302FB413-DA69-4408-A60E-035205FF94A7',1016,N'KG90N'
Go
--> --> (Roy)生成測試數據
 
if not object_id('TeminalAttribute') is null
drop table TeminalAttribute
Go
Create table TeminalAttribute([tid] uniqueidentifier,[aid] int,[vvlaue] nvarchar(4))
Insert TeminalAttribute
select N'5AC5535E-BA6E-41BC-9314-00575984812E',1,N'银/蓝' union all
select N'FAECAF14-F379-48C3-8237-03472C3DD0BF',3,N'2580' union all
select N'302FB413-DA69-4408-A60E-035205FF94A7',4,N'2280' union all
select N'302FB413-DA69-4408-A60E-035205FF94A7',3,N'2980'
Go
--> --> (Roy)生成測試數據
 
if not object_id('AttributeDefine') is null
drop table AttributeDefine
Go
Create table AttributeDefine([aid] int,[aname] nvarchar(5))
Insert AttributeDefine
select 1,N'颜色' union all
select 3,N'建议市场价' union all
select 4,N'最高限价'
Go
declare @s nvarchar(4000)
set @s='select a.[tid],a.[vendor],a.[model]'
select @s=@s+','+quotename([aname])+'=max(case when b.[aid]='+rtrim([aid])+' then b.[vvlaue] else '''' end)'
from AttributeDefine
set @s=@s+' from Terminals a join TeminalAttribute b on a.[tid]=b.[tid] group by a.[tid],a.[vendor],a.[model]'
--print @s--显示语句
exec(@s)tid                                  vendor      model  颜色   建议市场价 最高限价
------------------------------------ ----------- ------ ---- ----- ----
5AC5535E-BA6E-41BC-9314-00575984812E 1001        K700c  银/蓝        
FAECAF14-F379-48C3-8237-03472C3DD0BF 1016        KE608N      2580  
302FB413-DA69-4408-A60E-035205FF94A7 1016        KG90N       2980  2280(3 行受影响)

Answer 8

--create table
create table Terminals
(
tid varchar(50),
vendor varchar(10),
model varchar(50)
)
create table TerminalAttribute
(
tid varchar(50),
aid int,
vvlaue varchar(50)
)
create table AttributeDefine
(
aid int,
aname varchar(50)
)
--insert test data
insert into Terminals
select '5AC5535E-BA6E-41BC-9314-00575984812E', '1001', 'K700c' union all
select 'AECAF14-F379-48C3-8237-03472C3DD0BF', '1016', 'KE608N' union all
select '302FB413-DA69-4408-A60E-035205FF94A7', '1016', 'KG90N'
insert into TerminalAttribute
select '5AC5535E-BA6E-41BC-9314-00575984812E', 1, '银/蓝' union all
select 'FAECAF14-F379-48C3-8237-03472C3DD0BF', 3, '2580' union all
select '302FB413-DA69-4408-A60E-035205FF94A7', 4, '2280' union all
select '302FB413-DA69-4408-A60E-035205FF94A7', 3, '2980'
insert into AttributeDefine
select 1, N'颜色' union all
select 3, N'建议市场价' union all
select 4, N'最高限价'
--query data
select
a.tid,
a.vendor,
a.model,
N'颜色' = (case when b.aid = 1 then b.vvlaue else 'N/A' end),
N'建议市场价' = (case when b.aid = 3 then b.vvlaue else 'N/A' end),
N'最高限价' = (case when b.aid = 4 then b.vvlaue else 'N/A' end)
from terminals a
inner join TerminalAttribute b
on a.tid = b.tid
inner join AttributeDefine c
on b.aid = c.aid
--result
/*
tid                                     wendor  model   颜色  建议市场价  最高限价 
5AC5535E-BA6E-41BC-9314-00575984812E 1001 K700c 银/蓝 N/A N/A
302FB413-DA69-4408-A60E-035205FF94A7 1016 KG90N N/A N/A 2280
302FB413-DA69-4408-A60E-035205FF94A7 1016 KG90N N/A 2980 N/A
*/--drop table
drop table Terminals
drop table TerminalAttribute
drop table AttributeDefine