more efficiently pivot rows

Question

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.

Presumably (can you check though) it's the pivot that's taking the time rather than the CTE however I expect you can simplify the Value= line in the CTE with COALESCE([CharV],[NumV],0), see if that has any effect. — Rikalous, Feb 03 '17 at 16:16
Unfortunately, that won't work because none of the fields are truly nulls. If CharV doesn't have a value then it's an empty string "" and if NumV doesn't have a value it's just 0. — Hasib_Ibradzic, Feb 03 '17 at 16:20

John Cappelletti · Accepted Answer · 2017-02-03T17:10:24.807

2

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

edited Feb 03 '17 at 17:10

answered Feb 03 '17 at 16:50

John Cappelletti

79,615
7
44
66

For this approach would the Union All be inside the CTE, or is that for the select statement after? – Hasib_Ibradzic Feb 03 '17 at 16:55
@VBA_SQL_Programmer I've seen performance suffer with large CTEs. See updated answer for the two approaches – John Cappelletti Feb 03 '17 at 17:03
@VBA_SQL_Programmer I don't have a sense for how may rows you are dealing with, but, THERE IS NO SHAME IN TEMP tables – John Cappelletti Feb 03 '17 at 17:05
@VBA_SQL_Programmer Just curious if you witnessed a performance gain – John Cappelletti Feb 03 '17 at 17:14
@VBA_SQL_Programmer Still seems high to me, but again, I don't know the size of your table. – John Cappelletti Feb 03 '17 at 18:41
it's a vary large table. Like I said every ID has about 1300 rows (I am only pulling about 300 to use as columns). And there are almost 500 ID's. Do you still think that's to long? – Hasib_Ibradzic Feb 03 '17 at 18:49
1

@VBA_SQL_Programmer Still shouldn't be 10 minutes. Perhaps filter on the 300 to reduce the number of rows – John Cappelletti Feb 03 '17 at 18:57

more efficiently pivot rows

1 Answers1