Column horizontal display problems

Category: MS-SQL Server -> Foundation Classes Author: fsmkayc Date: 2006-07-13 16:13:46
 
fsmkayc
2006-07-13 16:13:46
Table T
Supplier Name offer
G1 C1 10
G2 C1 11
G3 C1 12
G1 C2 10
G2 C2 11
G3 C2 12
G1 C3 14
G3 C3 12
I now put suppliers out how to do landscape?
if I do not know the number of suppliers in the conditions using dynamic SQL generation ;
Name G1 G2 G3
C1 10 11 12
C2 ; 10 11 12
C3 14 0 12

I see Zou Construction of the book << SQL Server200 Development and Management >> friend on examples,
but did not understand! alas ~

jjjkk123456789
2006-07-13 16:29:02
Declare @ S Nvarchar (4000)
Select @ S = 'Select Name'
Select @ S = @ S + ', ; SUM (Case supplier When'' '+ suppliers +''' Then Quote Else 0 End) As' + Supplier
From T Group By supplier
Select @ S = @ S + 'From T Group By Name ; Order By Product Name '
EXEC (@ S)
neng308
2006-07-13 16:33:49
create table test (vendor char (2), [group] char (2), price decimal (10,1))
insert test select 'G1', 'C1', 10.0
union all select 'G2', 'C1', 11.0
union ; all select 'G3', 'C1', 12.0
union all select 'G1', 'C2', 10.0
union all select 'G2', 'C2' , 11.0
union all select 'G3', 'C2', 12.0
union all select 'G1', 'C3', 14.0
union all select 'G3', 'C3', 12.0

Declare @ s nvarchar (4000)
set @ s = 'select [group]'
select @ s = @ s + ',' + quotename (vendor)
+ N '= sum (case vendor when' + quotename (vendor, N'''')
+ N ' then price end) '
from test
group by vendor
exec (@ s + N'
from test
group by ; [group] ')

drop table test

group G1 ; G2 G3
C1 10.0 11.0 12.0
C2 10.0 11.0 12.0
C3 14.0 NULL 12.0
aq777888
2006-07-13 16:53:34
this in the FAQ There are many examples of the

SQL Server 2005 provides a pivot / unpivot keyword ranks conversion even easier
Chaneoun08
2006-07-13 17:11:02
Why does it want to add this one:
Case Supplier When'' '+ suppliers +''' Then Quote Else 0 End
What does it mean?
centrality_wu
2006-07-13 17:13:30
Supplier Name offer
G1 C1 10
G2 C1 11

Case suppliers When 'G1' Then Quote Else 0 End
Case Supplier When 'G2' Then Quote Else 0 End

print @ s

can see the statement
yangjing1423
2006-07-13 17:22:12
study hard, I like to write according to some, always prompt me invalid column name retailers, not as, directly with the field name is the same prompt, you brother, why ah?

declare @ sqlText nvarchar (2000)
select @ sqlText = 'select turns,'
select @ sqlText = @ sqltext
+ '(CASE retailers WHEN''' + retailer +'' 'THEN amount ELSE 0 END) AS''' + retailers
+ 'from (select distinct b.MemberName as retailers, amount from t_indent as a inner join t_member as b on a. [from ] = b.id where a. [to] = 2) '

select @ sqlText = @ sqlText +' from t_indent '
print ; @ sqltext

exec (@ sqlText)
go
zhazhentao1
2006-07-13 17:35:27
try

statement that there were several errors

declare @ sqlText nvarchar (2000)
select @ sqlText = 'select turns'
select @ sqlText = @ sqltext
+', SUM (CASE retailers WHEN'' '+ retailer +''' THEN amount ELSE 0 END) AS ['+ retailer + ']'
from (select distinct b.MemberName as retailers, amount from t_indent as a inner join t_member as b on a . [from] = b.id where a. [to] = 2) A

select @ sqlText = @ sqlText + 'from t_indent group by turns'
print @ sqltext

exec (@ sqlText)
go