CODEWITHSUNDEEP
sqlsql-serversql-server-2012pivot
Hasib_Ibradzic
Hasib_Ibradzic

Reputation: 666

more efficiently pivot rows

I am trying to join multiple tables together. One of the tables I am trying to join has hundreds of rows per ID of data. I am trying to pivot about 100 rows for each ID into columns. The value I am trying to use isn't always in the same row. Below is an example (my real table has hundreds of rows per ID). AccNum for example in ID 1 may be in the NumV column, but for ID 2 it may be in the CharV column.

ID  QType       CharV       NumV
1   AccNum                  10
1   EmpNam      John Inc    0
1   UW          Josh        0
2   AccNum      11
2   EmpNam      CBS         0
2   UW          Dan         0

The original code I used was a select statement with hundreds of lines like one below:

Max(Case When PM.[QType] = 'AccNum' Then NumV End) as AccNum

This code with hundreds on lines completed in just under 10 min. The problem however is that in only pulls in values from the column I specify, so I will always loss the data that is in a different column. (In the example above I would get AccNum 10, but not AccNum11 because it's in the CharV column).

I updated the code to use a pivot:

;with CTE
As
(
    Select [PMID], [QType],
        Value=concat(Nullif([CharV],''''),Nullif([NumV],0)) 
        From [DBase].[dbo].[PM]
)

Select C.[ID] AS M_ID
    ,Max(c.[AccNum]) As AcctNum
    ,Max(c.[EmpNam]) As EmpName

and so on...

I then select all of my hundreds of rows and then pivot it the data:

from CTE
pivot (max(Value) for [QType] in ([AccNum],[EmpNam],(more rows)))As c

The problem with this code, however, is that it takes almost 2 hours to run.

Is there a different, more efficient solution to what I am trying to accomplish? I need to have the speed of the first code, but the result of the second.

Upvotes: 3

Views: 73

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 82000

Perhaps you can reduce the Concat/NullIf processing by using a UNION ALL

Select ID,QType,Value=CharV From @YourTable where CharV>''
Union All
Select ID,QType,Value=cast(NumV as varchar(25))  From @YourTable where NumV>0

For the conditional aggregation approach

No need to worry about which field, just reference VALUE

Select [ID]
      ,[Accnum] = Max(Case When [QType] = 'AccNum' Then Value End) 
      ,[EmpNam] = Max(Case When [QType] = 'EmpNam' Then Value End) 
      ,[UW]     = Max(Case When [QType] = 'UW'     Then Value End) 
 From (
        Select ID,QType,Value=CharV From @YourTable where CharV>''
        Union All
        Select ID,QType,Value=cast(NumV as varchar(25))  From @YourTable where NumV>0
     ) A
 Group By ID

For the PIVOT approach

Select [ID],[AccNum],[EmpNam],[UW]
From (
        Select ID,QType,Value=CharV From @YourTable where CharV>''
        Union All
        Select ID,QType,Value=cast(NumV as varchar(25))  From @YourTable where NumV>0
     ) A
 Pivot (max([Value]) For [QType] in ([AccNum],[EmpNam],[UW])) p

Upvotes: 2

Related Questions