March 27, 2013 at 2:44 pm
Hello All, how to show results in pivot format,
please assit me to get the desired output as attached
--create table #MedMediaTemp1 (PatientJoinDate Date,VisitType varchar(25),Priority varchar(12),Roller int,Total int)
insert into #MedMediaTemp1
select
'04/11/2013','Emergency Visit','Priority 1',1,34
union
select '04/11/2013','Emergency Visit','Priority 2',1,21
union
select '04/11/2013','Emergency Visit','Priority 3',1,67
union
select '04/11/2013','Emergency Visit','Priority 2',2,9
union
select '04/11/2013','Emergency Visit','Priority 3',2,21
union
select '04/11/2013','Emergency Visit','Priority 1',2,18
union
select '04/11/2013','Appointment Visit','Priority 2',1,7
union
select '04/11/2013','Appointment Visit','Priority 1',1,9
union
select '04/11/2013','Appointment Visit','Priority 3',1,217
union
select '04/11/2013','Appointment Visit','Priority 3',2,16
union
select '04/11/2013','Appointment Visit','Priority 2',2,1
union
select '04/11/2013','Appointment Visit','Priority 1',2,22
union
select '03/14/2013','Emergency Visit','Priority 3',1,64
union
select '03/14/2013','Emergency Visit','Priority 2',1,27
union
select '03/14/2013','Emergency Visit','Priority 1',1,33
union
select '03/14/2013','Emergency Visit','Priority 3',2,68
union
select '03/14/2013','Emergency Visit','Priority 2',2,68
union
select '03/14/2013','Emergency Visit','Priority 1',2,43
union
select '03/14/2013','Appointment Visit','Priority 2',1,10
union
select '03/14/2013','Appointment Visit','Priority 3',1,17
union
select '03/14/2013','Appointment Visit','Priority 1',1,11
union
select '03/14/2013','Appointment Visit','Priority 1',2,14
union
select '03/14/2013','Appointment Visit','Priority 3',2,56
union
select '03/14/2013','Appointment Visit','Priority 2',2,30
union
select '02/11/2013','Emergency Visit','Priority 2',1,56
union
select '02/11/2013','Emergency Visit','Priority 3',1,69
union
select '02/11/2013','Emergency Visit','Priority 1',1,352
union
select '02/11/2013','Appointment Visit','Priority 1',1,10
union
select '02/11/2013','Appointment Visit','Priority 2',1,54
union
select '02/11/2013','Appointment Visit','Priority 3',1,175
union
select '01/12/2013','Emergency Visit','Priority 2',1,20
union
select '01/12/2013','Emergency Visit','Priority 3',1,389
union
select '01/12/2013','Emergency Visit','Priority 1',1,642
union
select '01/12/2013','Appointment Visit','Priority 1',1,76
union
select '01/12/2013','Appointment Visit','Priority 2',1,48
union
select '01/12/2013','Appointment Visit','Priority 3',1,98
union
select '12/24/2012','Emergency Visit','Priority 2',1,5
union
select '12/24/2012','Emergency Visit','Priority 3',1,74
union
select '12/24/2012','Emergency Visit','Priority 1',1,36
union
select '12/24/2012','Appointment Visit','Priority 1',1,10
union
select '12/24/2012','Appointment Visit','Priority 2',1,10
union
select '12/24/2012','Appointment Visit','Priority 3',1,201
union
select '11/11/2012','Emergency Visit','Priority 1',1,26
union
select '11/11/2012','Emergency Visit','Priority 3',1,58
union
select '11/11/2012','Emergency Visit','Priority 2',1,3
union
select '11/11/2012','Appointment Visit','Priority 3',1,129
union
select '11/11/2012','Appointment Visit','Priority 2',1,15
union
select '11/11/2012','Appointment Visit','Priority 1',1,9
union
select '10/12/2012','Emergency Visit','Priority 1',1,20
union
select '10/12/2012','Emergency Visit','Priority 3',1,383
union
select '10/12/2012','Emergency Visit','Priority 2',1,38
union
select '10/12/2012','Appointment Visit','Priority 3',1,104union
select '10/12/2012','Appointment Visit','Priority 1',1,7
union
select '10/12/2012','Appointment Visit','Priority 2',1,10
union
select '09/13/2012','Emergency Visit','Priority 2',1,35
union
select '09/13/2012','Emergency Visit','Priority 3',1,64
union
select '09/13/2012','Emergency Visit','Priority 1',1,32
union
select '09/13/2012','Appointment Visit','Priority 1',1,86
union
select '09/13/2012','Appointment Visit','Priority 2',1,116
union
select '09/13/2012','Appointment Visit','Priority 3',1,156
union
select '08/11/2012','Emergency Visit','Priority 2',1,60
union
select '08/11/2012','Emergency Visit','Priority 1',1,3union
select '08/11/2012','Emergency Visit','Priority 3',1,69
union
select '08/11/2012','Appointment Visit','Priority 2',1,61
union
select '08/11/2012','Appointment Visit','Priority 1',1,5union
select '08/11/2012','Appointment Visit','Priority 3',1,100
union
select '07/20/2012','Emergency Visit','Priority 2',1,298
union
select '07/20/2012','Emergency Visit','Priority 3',1,175
union
select '07/20/2012','Emergency Visit','Priority 1',1,52
union
select '07/20/2012','Appointment Visit','Priority 3',1,64
union
select '07/20/2012','Appointment Visit','Priority 1',1,27
union
select '07/20/2012','Appointment Visit','Priority 2',1,49
union
select '06/10/2012','Emergency Visit','Priority 2',1,147
union
select '06/10/2012','Emergency Visit','Priority 3',1,618
union
select '06/10/2012','Emergency Visit','Priority 1',1,20
union
select '06/10/2012','Appointment Visit','Priority 2',1,12
union
select '06/10/2012','Appointment Visit','Priority 1',1,9
union
select '06/10/2012','Appointment Visit','Priority 3',1,88
SELECT * FROM #MedMediaTemp1
from this table how to produce the below results through sql please assist me
April 2013, Roller 1Aprill 2013 Roler 2April 2013 TotalMarch 2013 Roller 1March 2013 Roler 2March 2013 Total
Appointment Visit - ALL2333927238100138
Appointment Visit - Priority 192231111425
Appointment Visit - Priority 2718103040
Appointment Visit - Priority 321716233175673
Emergency Visit - ALL12248170124179303
Emergency Visit - Priority 1341852334376
Emergency Visit - Priority 221930276895
Emergency Visit - Priority 36721886468132
Thanks in advance
dhani
March 27, 2013 at 2:51 pm
Hi, below I pasted 3 examples of Pivot (sample 2 is shows you that a case statement can be used to replace Pivot i you don't like Pivot method).
-- sample 1
USE AdventureWorks
GO
SET ANSI_WARNINGS OFF
SELECT CustomerId,
SUM([Q2001]) AS Qty2001,
SUM([Q2002]) AS Qty2002,
SUM([V2001]) AS Val2001,
SUM([V2002]) AS Val2002
FROM (
SELECT H.CustomerId,
SUM(D.OrderQty) AS TotalQty,
SUM(D.LineTotal) AS TotalVal,
'Q'+CONVERT(VARCHAR(4),H.OrderDate,120) AS QYear,
'V'+CONVERT(VARCHAR(4),H.OrderDate,120) AS VYear
FROM Sales.SalesOrderDetail AS D INNER JOIN
Sales.SalesOrderHeader AS H ON D.SalesOrderId = H.SalesOrderId
WHERE D.ProductId=771
AND H.OrderDate >='20010101'
AND H.OrderDate <'20030101'
GROUP BY H.CustomerId,
CONVERT(VARCHAR(4),H.OrderDate,120)
) Main
PIVOT
(
SUM(TotalQty)
FOR QYear IN ([Q2001],[Q2002])
) PQ
PIVOT
(
SUM(TotalVal)
FOR VYear IN ([V2001],[V2002])
) PV
GROUP BY CustomerId
ORDER BY CustomerId
GO
----------------------------------------------------------
-- sample 2
USE AdventureWorks
GO
SELECT H.CustomerId,
SUM(CASE YEAR(H.OrderDate)
WHEN 2001
THEN D.OrderQty
END) AS Qty2001,
SUM(CASE YEAR(H.OrderDate)
WHEN 2002
THEN D.OrderQty
END) AS Qty2002,
SUM(CASE YEAR(H.OrderDate)
WHEN 2001
THEN D.LineTotal
END) AS Val2001,
SUM(CASE YEAR(H.OrderDate)
WHEN 2002
THEN D.LineTotal
END) AS Val2002
FROM Sales.SalesOrderDetail AS D INNER JOIN
Sales.SalesOrderHeader AS H ON D.SalesOrderId = H.SalesOrderId
WHERE D.ProductId=771
AND H.OrderDate >='20010101'
AND H.OrderDate <'20030101'
GROUP BY H.CustomerId
ORDER BY H.CustomerId
GO
----------------------------------------------------------
-- sample 3
with CustTotals as
(
select CustomerID
,str(year(H.OrderDate),4) as Yr
,sum(D.OrderQty) as TotalQty
,sum(D.LineTotal) as TotalVal
from Sales.SalesOrderDetail D
join Sales.SalesOrderHeader H
on D.SalesOrderID=H.SalesOrderID
where D.ProductID=771
and H.OrderDate>='20010101'
and H.OrderDate <'20030101'
group by H.CustomerID
,year(H.OrderDate)
)
select
P1.CustomerID,Qty2001,Qty2002,Val2001,Val2002
from
(select CustomerID, PivotKey='Qty'+Yr, TotalQty
from CustTotals) I
pivot (sum(TotalQty) for PivotKey in ([Qty2001],[Qty2002])) P1
join
(select CustomerID, PivotKey='Val'+Yr, TotalVal
from CustTotals) I
pivot (sum(TotalVal) for PivotKey in ([Val2001],[Val2002])) P2
on P1.CustomerID=P2.CustomerID;
----------------------------------------------------------
-- explanation of sample 3 (taken from CustTotals):
select CustomerID
,str(year(H.OrderDate),4) as Yr
,sum(D.OrderQty) as TotalQty
,sum(D.LineTotal) as TotalVal
from Sales.SalesOrderDetail D
join Sales.SalesOrderHeader H
on D.SalesOrderID=H.SalesOrderID
where D.ProductID=771
and H.OrderDate>='20010101'
and H.OrderDate <'20030101'
group by H.CustomerID
,year(H.OrderDate)
output:
CustID Yr Qty Value
20200124079.988000
29200148159.976000
20200222592.492375
29200212039.994000
full query output:
CustID Qty2001 Qty2001 Val2001 Val2002
20224079.9880002592.492375
29418159.9760002039.994000
March 27, 2013 at 3:45 pm
Hi Kevin,
thank you veru much for your immediate response, could you please see the attached expected result,
if possible please get the results like that
i greatful to your help
thanks alot
best regards
dhani
March 27, 2013 at 3:55 pm
asita (3/27/2013)
Hi Kevin,thank you veru much for your immediate response, could you please see the attached expected result,
if possible please get the results like that
i greatful to your help
thanks alot
best regards
dhani
Really? You are the one who will need to support this code I would recommend that you try to modify the code to meet your requirements and if you have problems, come back to this thread and show us what you have done and where you are having problems.
Something to do with giving a fish and teaching to fish...
March 27, 2013 at 7:45 pm
Thank you Lynn for your advice,
sure i am on my way to working on, just in case
i will goahead with what kevin provided
Thanks again for your advice
dhani
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply