sql server sort dynamic pivot on large set of data

Question

I am having trouble sorting a pivot based on a quite large set of data. I have looked at many examples, but none of them seems to address the issue of volume - or perhaps I am just missing something. I have had a very good look here: Sort Columns For Dynamic Pivot and PIVOT in sql 2005 and found much good advise, but I still cannot find the correct way to sort my pivot.

I am using the following sql. It pivots the columns, but the result needs to be sorted for readability:

SELECT a.* INTO #tempA 
FROM (SELECT top (5000) id, email, CONVERT(varchar,ROW_NUMBER() OVER 
(PARTITION BY email ORDER BY id)) AS PIVOT_CODE FROM Email) a 
order by PIVOT_CODE

DECLARE @cols AS NVARCHAR(MAX),
@sql  AS NVARCHAR(MAX)

SELECT @cols =STUFF((SELECT DISTINCT ', ' + QUOTENAME(col)
                FROM #tempA WITH (NOLOCK)
                cross apply
                (
                    SELECT 'id_' + PIVOT_CODE, id

                ) c (col, so)
                group by col, so
                --order by col

        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

set @sql = 'SELECT email, '
+@cols+
'INTO ##AnotherPivotTest FROM
(
   SELECT email,
       col,
       value
   FROM #tempA WITH (NOLOCK)
   cross apply
   (
    values
      (''id_'' + PIVOT_CODE, id)
   ) c (col, value)
) d
pivot
(
  max(value)
  for col in ('
            + @cols+            
            ')
) piv'

EXEC (@sql)

SELECT * FROM ##AnotherPivotTest

The result is a chaos to look at:

==============================================================================================
| email    | id_19 | id_24 | id_2 | id_16 | id_5 | id_9 | id_23 | .... | id_1 | .... | id_10 |
==============================================================================================
| xx@yy.dk | NULL  | NULL  | NULL | NULL  | NULL | NULL | NULL  | NULL | 1234 | NULL | NULL  |
==============================================================================================

I would very much like the Ids to be sorted - beginning with id_1.

As you can see, I have attempted to place an 'order by' in the selection for 'cols', but that gives me the error: "ORDER BY items must appear in the select list if SELECT DISTINCT is specified." And without DISTINCT, I get another error: "The number of elements in the select list exceeds the maximum allowed number of 4096 elements."

I'm stuck, so any help will be greatly appreciated!

score 1 · Answer 1 · answered Oct 27 '17 at 07:14

1

Not sure what causes the problem but I've solved my order problem in my pivot table by inserting the data coming from tempA into another temp table and ordering them there

 INSERT INTO #tempB
    SELECT * FROM #tempA
    ORDER BY PIVOT_CODE

Then selecting distinct ones like so:

    SELECT @cols = @cols + QUOTENAME(PIVOT_CODE) + ',' FROM (SELECT DISTINCT PIVOT_CODE FROM #tempB ORDER BY PIVOT_CODE)
    SELECT @cols = SUBSTRING(@cols, 0, LEN(@cols)) --trims "," at end

answered Oct 27 '17 at 07:14

etuncoz

198
3
7

Well... the values gets sorted nicely in #tempB. But after pivoting #tempB, id_2 now comes straight after id_19, followed by id_16... Strange... – Oct 27 '17 at 08:36

score 1 · Answer 2 · answered Oct 27 '17 at 07:19

You can also just use a cursor to determine your cols and the order them

Cursor with cols ordered

  declare @gruppe nvarchar(max)
  declare @gruppeSql nvarchar(max)
  declare @SQL nvarchar(max)

  DECLARE myCustomers CURSOR FOR
  select top 10  FirstName from [dbo].[DimCustomer] Order by FirstName

  set @gruppeSql = ''

  OPEN myCustomers
   FETCH NEXT FROM myCustomers INTO @gruppe
  IF (@@FETCH_STATUS>=0)
  BEGIN
  SET @gruppeSql = @gruppeSql +'[' +@gruppe+']'
  FETCH NEXT FROM myCustomers INTO @gruppe
   END
   WHILE (@@FETCH_STATUS<>-1)
   BEGIN
   IF (@@FETCH_STATUS<>-2)

   SET @gruppeSql = @gruppeSql  + ',[' +@gruppe+']'
   FETCH NEXT FROM myCustomers INTO @gruppe
   END
   CLOSE myCustomers
   DEALLOCATE myCustomers 

  SET @gruppeSql = replace(@gruppesql,'''','')
  /*Select to preview your cols*/
  select @gruppeSql

Dynamic pivot

       SET @SQL = '
   Select *
  from
  (
   SELECT SalesAmount, FirstName
    FROM [AdventureWorksDW2014].[dbo].[FactInternetSales] a inner join dbo.DimCustomer b on a.CustomerKey = b.CustomerKey

  ) x
  pivot
  (
    sum(SalesAmount)
    for FirstName in ('+@gruppesql+')  
  ) p'

  print @sql
  exec(@sql)

sql server sort dynamic pivot on large set of data

2 Answers2