如何在没有聚合函数的SQL Server中创建数据透视查询 [英] How to create a pivot query in SQL server without aggregate function

问题描述

我正在使用MS SQL SERVER，我有以下数据：



ValDate月份CurID SpotRate_Cad

11/1/2016 11 cad 1.00000000

11/1/2016 11 usd 1.30000000

11/1/2016 11 eur 1.51000000

11/2/2016 11 cad 1.00000000

11/2/2016 11 usd 1.31000000

11/2/2016 11 eur 1.50000000



我想要有这种格式



日期月cad usd eur

11/1/2016 11 1.00000000 1.30000000 1.51000000

11/1/2016 11 1.00000000 1.31000000 1.50000000



我尝试过：



SELECT *

FROM（

SELECT npv.ValDate，月份（npv.ValDate）为月，rtrim（npv.CurID）为CurID，（npv.SpotRate） ）as SpotRate_Cad



来自boss_bo.dbo.NPVPLFxRate为npv

LEFT JOIN boss_bo.dbo.NPVPLFxRate as npv2

ON（npv.ValDate = npv2.ValDate）



W这里（npv.ValDate< ='2017-06-20'）

AND（npv.ValDate> ='2016-11-01'）

AND（npv2。 CurID =usd）

）fx



PIVOT（

ValDate，Month，SpotRate
FOR CurID IN

（[cad]，[usd]，[eur]）

）AS PVT

解决方案

 CREATE TABLE #thetable 
（id INT IDENTITY（1,1）NOT NULL，
 ValDate DateTime，
 [Month] INT，
 CurID CHAR（3），
 SpotRate FLOAT 
）
 
 INSERT INTO #thetable（ValDate，[Month]，CurID，SpotRate）VALUES（'11 / 1 / 2016'，11，'cad'，1.0）
 INSERT INTO #thetable（ValDate，[Month]，CurID，SpotRate）VALUES（'11 / 1/2016'，11，'usd'，1.3）
 INSERT INTO #thetable（ValDate，[Month]，CurID，SpotRate）VALUES（'11 / 1/2016'，11，'eur'，1.51）
 INSERT INTO #thetable（ValDate，[Month]， CurID，SpotRate）VALUES（'11 / 2/2016'，11，'cad'，1.0）
 INSERT INTO #thetab le（ValDate，[Month]，CurID，SpotRate）VALUES（'11 / 2/2016'，11，'usd'，1.31）
 INSERT INTO #thetable（ValDate，[Month]，CurID，SpotRate）VALUES （'11 / 2/2016'，11，'eur'，1.5）
 
 
 SELECT * FROM #thetable 
 
 SELECT 
 dateadd（day ，datediff（day，0，t.ValDate），0）作为DATE 
，[月] 
，SUM（cad）作为cad 
，SUM（usd）作为usd 
 ，SUM（eur）as eur 
 FROM 
（
 SELECT 
 ValDate 
，[Month] 
，CASE WHEN CurID ='cad'THEN SpotRate END AS'cad'
，CASE WHEN CurID ='usd'那么SpotRate END AS'usd'
，CASE WHEN CurID ='eur'那么SpotRate END AS'eur'
 FROM #thetable 
）t 
 GROUP BY 
 dateadd（day，datediff（day，0，t.ValDate），0）
，[Month] 

I am using MS SQL SERVER and I have following data:

ValDate Month CurID SpotRate_Cad
11/1/2016 11 cad 1.00000000
11/1/2016 11 usd 1.30000000
11/1/2016 11 eur 1.51000000
11/2/2016 11 cad 1.00000000
11/2/2016 11 usd 1.31000000
11/2/2016 11 eur 1.50000000

I would like to have this format

Date Month cad usd eur
11/1/2016 11 1.00000000 1.30000000 1.51000000
11/1/2016 11 1.00000000 1.31000000 1.50000000

What I have tried:

SELECT *
FROM (
SELECT npv.ValDate, month(npv.ValDate) as Month, rtrim(npv.CurID) as CurID, (npv.SpotRate) as SpotRate_Cad

FROM boss_bo.dbo.NPVPLFxRate as npv
LEFT JOIN boss_bo.dbo.NPVPLFxRate as npv2
ON (npv.ValDate =npv2.ValDate)

WHERE (npv.ValDate<='2017-06-20')
AND (npv.ValDate>='2016-11-01')
AND (npv2.CurID = "usd")
) fx

PIVOT (
ValDate, Month, SpotRate
FOR CurID IN
([cad], [usd], [eur])
) AS PVT

解决方案

CREATE TABLE #thetable 
( id            INT IDENTITY(1,1) NOT NULL ,
ValDate         DateTime,
[Month]         INT,
CurID           CHAR(3),
SpotRate        FLOAT
)

INSERT INTO #thetable (ValDate, [Month], CurID, SpotRate) VALUES ('11/1/2016',11, 'cad', 1.0)
INSERT INTO #thetable (ValDate, [Month], CurID, SpotRate) VALUES ('11/1/2016',11, 'usd', 1.3)
INSERT INTO #thetable (ValDate, [Month], CurID, SpotRate) VALUES ('11/1/2016',11, 'eur', 1.51)
INSERT INTO #thetable (ValDate, [Month], CurID, SpotRate) VALUES ('11/2/2016',11, 'cad', 1.0)
INSERT INTO #thetable (ValDate, [Month], CurID, SpotRate) VALUES ('11/2/2016',11, 'usd', 1.31)
INSERT INTO #thetable (ValDate, [Month], CurID, SpotRate) VALUES ('11/2/2016',11, 'eur', 1.5)


SELECT * FROM #thetable

SELECT
   dateadd(day,datediff(day,0, t.ValDate ),0) as DATE
   ,[Month]
   ,SUM(cad) as cad
   ,SUM(usd) as usd
   ,SUM(eur) as eur
FROM
(
SELECT
  ValDate
  ,[Month]
  ,CASE WHEN CurID = 'cad' THEN SpotRate END AS 'cad'
  ,CASE WHEN CurID = 'usd' THEN SpotRate END AS 'usd'
  ,CASE WHEN CurID = 'eur' THEN SpotRate END AS 'eur'
FROM  #thetable
) t
GROUP BY 
dateadd(day,datediff(day,0, t.ValDate ),0)
,[Month]

这篇关于如何在没有聚合函数的SQL Server中创建数据透视查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！