在SQL中的数据透视表联接 [英] Pivot table join in sql
问题描述
我有表Tbl_Login
I Have Table Tbl_Login
Cat_ID int
Amount1 decimal
Amount2 decimal
Amount3 decimal
dateon date
Cat_ID Amount1 Amount2 Amount3 dateon
1 10 12 12 2013-02-12
2 10 12 12 2013-02-12
3 10 12 12 2013-02-12
4 10 12 12 2013-02-12
1 20 22 22 2013-02-13
2 20 22 22 2013-02-13
3 20 22 22 2013-02-13
5 20 22 22 2013-02-13
I want To Mamber Enter Two Date 12/02/2013 15/02/2013 and
colum1 Cat_ID 12-02-2013 13-02-2013 14-02-2013 15-02-2013
Amount1 1 10 20 null null
Amount2 1 12 22 null null
Amount3 1 12 22 null null
Amount1 2 10 20 null null
Amount2 2 12 22 null null
Amount3 2 12 22 null null
Amount1 3 10 20 null null
Amount2 3 12 22 null null
Amount3 3 12 22 null null
Amount1 4 10 null null null
Amount2 4 12 null null null
Amount3 4 12 null null null
Amount1 5 null 20 null null
Amount2 5 null 22 null null
Amount3 5 null 22 null null
推荐答案
您没有指定要使用的RDBMS，但是有几种方法可以将这些数据转换为所需的结果.
You did not specify what RDBMS you are using but there are several ways that you can transform this data into the result that you need.
如果使用的数据库不具有PIVOT函数，则可以通过使用UNION ALL查询取消数据透视，然后使用带有CASE表达式的聚合函数来旋转数据来完成此操作.日期列.该查询将类似于以下内容:
If you are using a database that does not have a PIVOT function, then this can be done by using a UNION ALL query to unpivot the data and then use an aggregate function with a CASE expression to pivot the dates into columns. The query will be similar to this:
select col,
cat_id,
max(case when dateon = '2013-02-12' then value end) [2013-02-12],
max(case when dateon = '2013-02-13' then value end) [2013-02-13],
max(case when dateon = '2013-02-14' then value end) [2013-02-14],
max(case when dateon = '2013-02-15' then value end) [2013-02-15]
from
(
select cat_id, 'amount1' col, amount1 value, dateon
from tbl_login
where dateon >= '2013-02-12'
and dateon <= '2013-02-15'
union all
select cat_id, 'amount2' col, cast(amount2 as decimal(10,2)) value, dateon
from tbl_login
where dateon >= '2013-02-12'
and dateon <= '2013-02-15'
union all
select cat_id, 'amount3' col, cast(amount3 as decimal(10,2)) value, dateon
from tbl_login
where dateon >= '2013-02-12'
and dateon <= '2013-02-15'
) src
group by col, cat_id
order by cat_id, col
请参见带有演示的SQL小提琴
如果使用的是SQL Server 2005+或Oracle 11g +，则可以同时使用UNPIVOT和PIVOT函数:
If you are using SQL Server 2005+ or Oracle 11g+, then you can use both the UNPIVOT and the PIVOT function:
select col, cat_id,
[2013-02-12], [2013-02-13],
[2013-02-14], [2013-02-15]
from
(
select cat_id, dateon,
col, value
from
(
select cat_id, amount1,
cast(amount2 as decimal(10,2)) amount2,
cast(amount3 as decimal(10,2)) amount3,
dateon
from tbl_login
where dateon >= '2013-02-12'
and dateon <= '2013-02-15'
) s
unpivot
(
value
for col in (Amount1, Amount2, Amount3)
) unpiv
) src
pivot
(
max(value)
for dateon in ([2013-02-12], [2013-02-13],
[2013-02-14], [2013-02-15])
) piv
order by cat_id, col
请参见带演示的SQL提琴.
如果要转换为列的日期数量未知，则可以使用动态sql(注意:动态代码是sql server语法):
If you have an unknown number of dates that you want to convert to columns, then you can use dynamic sql (note: dynamic code is sql server syntax):
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@startdate datetime,
@enddate datetime
set @startdate ='2013-02-12'
set @enddate ='2013-02-15'
;with dates (dt) as
(
select @startdate
union all
select dateadd(dd, 1, dt)
from dates
where dateadd(dd, 1, dt) <= @enddate
)
select dt
into #temp
from dates
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(convert(varchar(10), dt, 120))
from #temp
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT col, cat_id,' + @cols + '
from
(
select cat_id, dateon,
col, value
from
(
select cat_id, amount1,
cast(amount2 as decimal(10,2)) amount2,
cast(amount3 as decimal(10,2)) amount3,
dateon
from tbl_login
where dateon >= '''+convert(varchar(10), @startdate, 120)+'''
and dateon <= '''+convert(varchar(10), @enddate, 120)+'''
) s
unpivot
(
value
for col in (Amount1, Amount2, Amount3)
) unpiv
) src
pivot
(
max(value)
for dateon in (' + @cols + ')
) p
order by cat_id, col'
execute(@query)
请参见带有演示的SQL小提琴
所有查询的结果是:
| COL | CAT_ID | 2013-02-12 | 2013-02-13 | 2013-02-14 | 2013-02-15 |
------------------------------------------------------------------------
| amount1 | 1 | 10 | 20 | (null) | (null) |
| amount2 | 1 | 12 | 22 | (null) | (null) |
| amount3 | 1 | 12 | 22 | (null) | (null) |
| amount1 | 2 | 10 | 20 | (null) | (null) |
| amount2 | 2 | 12 | 22 | (null) | (null) |
| amount3 | 2 | 12 | 22 | (null) | (null) |
| amount1 | 3 | 10 | 20 | (null) | (null) |
| amount2 | 3 | 12 | 22 | (null) | (null) |
| amount3 | 3 | 12 | 22 | (null) | (null) |
| amount1 | 4 | 10 | (null) | (null) | (null) |
| amount2 | 4 | 12 | (null) | (null) | (null) |
| amount3 | 4 | 12 | (null) | (null) | (null) |
| amount1 | 5 | (null) | 20 | (null) | (null) |
| amount2 | 5 | (null) | 22 | (null) | (null) |
| amount3 | 5 | (null) | 22 | (null) | (null) |
这篇关于在SQL中的数据透视表联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
