查询数据(行到列的数据) [英] Querying Data (data from row to column)

问题描述

当前显示数据为:

Current showing data are:

col1 col2  col3  col4
1     AA    A001  300
2     AA    B001  200
3     AA    C001  450
4     BB    A001  230
5     BB    E001  300
6     CC    A001  200
7     CC    A002  300
...   ...   ...   ...
...   ...   ...   ...
...   ...   ...   ...

结果将是:

The result will be:

      A001 A002 B001 C001 E001 ...
AA    300  0    200  450  0    ...
BB    230  0    0    0    300  ...
CC    200  300  0    0    0    ...
...   ...  ...  ...  ...  ...  ...
...   ...  ...  ...  ...  ...  ...

我有上述问题.所以，请帮忙！

附注:AA，BB，CC ...以及A001，A002，B0001 ...都可以种植.我希望结果是动态增加行和列".

I have above problem. So, pls help!

P.S: AA,BB,CC,... and also A001,A002,B0001,... can be grown. I wish the result be "dynamically increase rows and columns".

推荐答案

有几种方法.

在SQL Server中透视数据 [

There are couple of ways.

Pivoting data in SQL Server[^]

create table #temptab
(
col1 int ,
col2  varchar(10),
col3  varchar(10),
col4 int
)
insert into #temptab
select 1,	  'AA',    'A001',  300
insert into #temptab
select 2,     'AA',    'B001',  200
insert into #temptab
select 3,     'AA',    'C001',  450
insert into #temptab
select 4,     'BB',    'A001',  230
insert into #temptab
select 5,     'BB',    'E001',  300
insert into #temptab
select 6,     'CC',    'A001',  200
insert into #temptab
select 7,     'CC',    'A002',  300
select COL2, ISNULL([A001],0),ISNULL([A002],0),ISNULL([B001],0),ISNULL([C001],0),ISNULL([E001],0) from
(select col2, col3, col4 
from #temptab) tab1
PIVOT (max(col4) FOR COL3 IN([A001],[A002],[B001],[C001],[E001]) ) TAB2

我认为这应该可以给您一个清晰的画面....

I think this should give u a clear picture....

您需要创建一个PIVOT表:MSDN有说明和一个小示例:

You need to create a PIVOT table: MSDN has instructions and a small example: Pivot table[^]

这篇关于查询数据(行到列的数据)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！