在一定的格式表的数据 [英] Table data in a certain format
问题描述
我试图让这是此格式的数据。
I am trying to get the data which is in this format
Tname FY FPY Week
test1 87 78 1
test1 45 34 2
test1 34 89 3
test1 34 56 4
test1 56 78 5
test1 68 45 6
test2 45 89 1
test2 76 65 2
test2 45 54 3
test3 56 57.8 2
test4 34 55 4
test4 32 52.2 7
test3 78 49.4 3
test5 89 46.6 2
test5 90 43.8 1
test5 98 41 6
test5 91 38.2 7
test6 92 35.4 5
test6 93 32.6 6
显示为
Tname week1 Week2 Week3 Week4 Week5 Week6 Week7
FY FPY FY FPY FY FPY FY FPY FY FPY FY FPY FY FPY
test1 87 78 45 34 34 89 34 56 56 78 68 45 NULL NULL
test2 45 89 76 65 45 54 NULL NULL NULL NULL NULL NULL NULL NULL
test3 NULL NULL 56 57.8 78 49.4 NULL NULL NULL NULL NULL NULL NULL NULL
test4 NULL NULL NULL NULL NULL NULL 34 55 NULL NULL NULL NULL 32 52.2
test5 90 43.8 89 46.6 NULL NULL NULL NULL NULL NULL 98 41 91 38.2
test6 NULL NULL NULL NULL NULL NULL NULL NULL 92 35.4 93 32.6 NULL NULL
我想实现这个使用存储过程或GridView控件。请告知
I would like achieve this using stored procedure or gridview. Please advise
推荐答案
您并没有具体的您所使用的RDBMS，但你的previous问题被标记与SQL Server所以这个答案假设为你的数据库。
You didn't specific what RDBMS you are using but your previous questions were tagged with SQL Server so this answer assumes that as your database.
有，你可以得到的结果的几种方法。一个简单的方法，是使用聚合函数类似于一项案件pression：
There are a few ways that you can get the result. One straightforward way, would be to use an aggregate function with a CASE expression similar to:
select tname,
sum(case when week = 1 then FY end) Week1FY,
sum(case when week = 1 then FPY end) Week1FPY,
sum(case when week = 2 then FY end) Week2FY,
sum(case when week = 2 then FPY end) Week2FPY,
sum(case when week = 3 then FY end) Week3FY,
sum(case when week = 3 then FPY end) Week3FPY,
sum(case when week = 4 then FY end) Week4FY,
sum(case when week = 4 then FPY end) Week4FPY,
sum(case when week = 5 then FY end) Week5FY,
sum(case when week = 5 then FPY end) Week5FPY,
sum(case when week = 6 then FY end) Week6FY,
sum(case when week = 6 then FPY end) Week6FPY,
sum(case when week = 7 then FY end) Week7FY,
sum(case when week = 7 then FPY end) Week7FPY
from yourtable
group by tname;
请参阅 SQL拨弄演示。
由于需要转动的两列，你首先会怎样UNPIVOT在 FY 和 FPY 列，然后应用旋转功能。再根据您的RDBMS，有几种方法可以UNPIVOT数据，我展示了如何使用CROSS APPLY和做UNION ALL：
Since you need to PIVOT on two columns, you will first what to unpivot the data in the FY and FPY columns, then apply the PIVOT function. Again depending on your RDBMS, there are several ways to unpivot data I am showing how to do it using CROSS APPLY and UNION ALL:
select tname,
col = 'week_'+cast(week as varchar(2))+'_'+col,
value
from yourtable
cross apply
(
select 'FY', FY union all
select 'FPY', FPY
) c (col, value)
该unpivot的过程中从多个列的数据转换成多行：
The unpivot process converts your data from the multiple columns into multiple rows:
| TNAME | COL | VALUE |
|-------|------------|-------|
| test1 | week_1_FY | 87 |
| test1 | week_1_FPY | 78 |
| test1 | week_2_FY | 45 |
| test1 | week_2_FPY | 34 |
| test1 | week_3_FY | 34 |
然后就可以应用旋转功能：
Then you can apply the PIVOT function:
select tname,
week_1_FY, week_1_FPY, week_2_FY, week_2_FPY,
week_3_FY, week_3_FPY, week_4_FY, week_4_FPY,
week_5_FY, week_5_FPY, week_6_FY, week_6_FPY,
week_7_FY, week_7_FPY
from
(
select tname,
col = 'week_'+cast(week as varchar(2))+'_'+col,
value
from yourtable
cross apply
(
select 'FY', FY union all
select 'FPY', FPY
) c (col, value)
) d
pivot
(
sum(value)
for col in (week_1_FY, week_1_FPY, week_2_FY, week_2_FPY,
week_3_FY, week_3_FPY, week_4_FY, week_4_FPY,
week_5_FY, week_5_FPY, week_6_FY, week_6_FPY,
week_7_FY, week_7_FPY)
) piv;
请参阅 SQL拨弄演示。
最后，如果一周的人数将是未知的，那么你可以使用动态SQL来得到结果：
Finally if the number of week will be unknown, then you can use dynamic SQL to get the result:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@weekstart int = 1,
@weekend int = 4
select @cols = STUFF((SELECT ',' + QUOTENAME('week_'+cast(week as varchar(2))+'_'+col)
from yourtable
cross apply
(
select 'FY', 1 union all
select 'FPY', 2
) c (col, so)
where week >= @weekstart
and week <= @weekend
group by col, so, week
order by week, so
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT tname, ' + @cols + '
from
(
select tname,
col = ''week_''+cast(week as varchar(2))+''_''+col,
value
from yourtable
cross apply
(
select ''FY'', FY union all
select ''FPY'', FPY
) c (col, value)
where week >= '+cast(@weekstart as varchar(2))+'
and week <= '+cast(@weekend as varchar(2))+'
) x
pivot
(
sum(value)
for col in (' + @cols + ')
) p '
execute sp_executesql @query;
请参阅 SQL拨弄演示。所有版本给的结果是：
See SQL Fiddle with Demo. All versions give a result of:
| TNAME | WEEK_1_FY | WEEK_1_FPY | WEEK_2_FY | WEEK_2_FPY | WEEK_3_FY | WEEK_3_FPY | WEEK_4_FY | WEEK_4_FPY | WEEK_5_FY | WEEK_5_FPY | WEEK_6_FY | WEEK_6_FPY | WEEK_7_FY | WEEK_7_FPY |
|-------|-----------|------------|-----------|------------|-----------|------------|-----------|------------|-----------|------------|-----------|------------|-----------|------------|
| test1 | 87 | 78 | 45 | 34 | 34 | 89 | 34 | 56 | 56 | 78 | 68 | 45 | (null) | (null) |
| test2 | 45 | 89 | 76 | 65 | 45 | 54 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
| test3 | (null) | (null) | 56 | 58 | 78 | 49 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
| test4 | (null) | (null) | (null) | (null) | (null) | (null) | 34 | 55 | (null) | (null) | (null) | (null) | 32 | 52 |
| test5 | 90 | 44 | 89 | 47 | (null) | (null) | (null) | (null) | (null) | (null) | 98 | 41 | 91 | 38 |
| test6 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | 92 | 35 | 93 | 33 | (null) | (null) |
这篇关于在一定的格式表的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
