question about pivot table? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

question about pivot table?

Author

Topic

Zeinab
Starting Member

7 Posts

Posted - 2008-11-01 : 08:23:12

Hello Friends,

I have a query like below,now I am trying to write this query with pivot table, would you please help me?

regards,

Select U.[ID],U.FirstName, U.LastName,
(Select Count(*) From History Where [Text]='REG' And UserID=U.[ID] ) As REG,
(Select Count(*) From History Where [Text]='CORRECTION REG' And UserID=U.[ID] ) As CorrectionREG,
(Select Count(*) From History Where [Text] Like '%SWAP%' And UserID=U.[ID] ) As SWAP,
(Select Count(*) From History Where [Text] Like '%Fer%' And UserID=U.[ID] ) As Transfer,
(Select Count(*) From History Where [Text] Like '%Other%' And UserID=U.[ID] ) As Other,
Count(*) As [Total]
From History H Inner join Users U On H.UserID=U.ID Inner join SIMCard3 M On M.MSISDN=H.MSISDN
Group By U.FirstName, U.LastName, U.[ID]
Order By U.LastName Asc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-01 : 11:43:48

i think this is what you want

Select U.[ID],U.FirstName, U.LastName,
SUM(CASE WHEN [Text]='REG' THEN 1 ELSE 0 END) As REG, 
SUM(CASE WHEN [Text]='CORRECTION REG' THEN 1 ELSE 0 END) As CorrectionREG, 
SUM(CASE WHEN [Text] Like '%SWAP%' THEN 1 ELSE 0 END) As SWAP, 
SUM(CASE WHEN [Text] Like '%Fer%'  THEN 1 ELSE 0 END) As Transfer, 
SUM(CASE WHEN [Text] Like '%Other%'THEN 1 ELSE 0 END) As Other, 
Count(*) As [Total] 
From History H Inner join Users U On H.UserID=U.ID Inner join SIMCard3 M On M.MSISDN=H.MSISDN 
Group By U.FirstName, U.LastName, U.[ID] 
Order By U.LastName Asc

Zeinab
Starting Member

7 Posts

Posted - 2008-11-04 : 01:25:59

Thanks for your help
I wrote this query to solve my problem ,the result is perfect but I dont want to use union in this query and I am trying to find another solution.hereunder I have the query and also the result.

Select 'Regiosteration' UserId, *
From
(
Select userID,CASE WHEN [Text]='REG' THEN 1 ELSE 0 END as REG
From History
)as T
PIVOT
(
Sum(REG) for UserId in ([D1007],[D0262],[D0753],[D0384])
)as P
union
Select 'Cor Regiosteration' UserId, *
From
(
Select userID,CASE WHEN [Text]='Correction REG' THEN 1 ELSE 0 END as CorREG
From History
)as T
PIVOT
(
Sum(CorREG) for UserId in ([D1007],[D0262],[D0753],[D0384])
)as P
union
Select 'Swap' UserId, *
From
(
Select userID,CASE WHEN [Text]like '%SWAP%' THEN 1 ELSE 0 END as Swap
From History
)as T
PIVOT
(
Sum(Swap) for UserId in ([D1007],[D0262],[D0753],[D0384])
)as P
union
Select 'Trasfer' UserId,*
From
(
Select userID,CASE WHEN [Text] like '%Fer%' THEN 1 ELSE 0 END as Fer
From History
)as T
PIVOT
(
Sum(Fer) for UserId in ([D1007],[D0262],[D0753],[D0384])
)as P

Result:

UserID D1007 D0262 D0753 D0384

Regiosteration 8737 1 0 0
Cor Regiosteration 48900 136 4562 1087
Swap 3972 0 9 0
Trasfer 23 0 0 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-04 : 01:44:26

did you try my suggestion?

Zeinab
Starting Member

7 Posts

Posted - 2008-11-04 : 02:16:59

yes , the result was the same first query that I wrote.(the query without pivot table)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-04 : 02:30:32

so is it a requirement that you should use PIVOT ?