I have a table in SQL Server that is horrid design, but am in need of a way to display ONE row per username with a breakdown of each item and qantity of that item the person sold. This is sample DDL

  Declare @Test Table (username varchar(10), itemsold varchar(100), amtsold integer)
Insert Into @Test (username, itemsold, amtsold) Values
('jj11', 'chair', 2), ('jj11', 'doghouse', 3), ('jj11', 'reindeer', 14)
,('ar44', 'bat', 1), ('ar44', 'cap', 4)

And this is how I want my data output

  UserName   Item1  Qty1   Item2     Qty2   Item3     Qty3
  jj11       chair  2      doghouse  3      reindeer  14
  ar44       bat    1      cap       4

I thought a pivot might be an option, but I can not wrap my mind around how to achieve such with a variable number of items. My actual production table has over 4K rows in it! So could potentially be listing out 50 to 60 items just depending

| improve this question | | | | |
  • 1
    Well, a pivot table generally gets built from a rowset with a label field and a value field. Can you present your data in the form (jj11, Item1, chair), (jj11, Qty1, 2), and so on? Getting from there to the pivoted table should be fairly easy, I would think. – RDFozz Nov 17 '17 at 17:37
  • @RDFozz -> Unfortunately not, I need Item, Qty to be headers. If it would help to make a temp table in the format you propose then pivot it out that would work as well – BellHopByDayAmetuerCoderByNigh Nov 17 '17 at 17:40
  • The pivot would turn the label column into headers; that's the point behind that intermediate step. – RDFozz Nov 17 '17 at 17:51
  • @RDFozz - ah then yes that would be perfectly acceptable – BellHopByDayAmetuerCoderByNigh Nov 17 '17 at 17:51
  • Point of clarification: If there was an additional entry in your @Test table, as follows: ('jj11', 'chair', 3), would you expect to see Item1 = 'chair', Qty1 = 5? Or two additional columns, Item4 = 'chair', Qty4 = 3? – RDFozz Nov 20 '17 at 18:15

To use the PIVOT command, you need to have the labels you want ([Item1], [Qty1], etc.) as values in the table. So, the first step is to get a version of the table where instead of:

 username | itemsold | amtsold
   jj11   | chair    |    2
   jj11   | doghouse |    3

You need the following:

 username |  label  |    value
   jj11   | Item1   | chair
   jj11   | Qty1    | 2
   jj11   | Item2   | doghouse
   jj11   | Qty2    | 3

Once the data is in that form, then the pivot statement has to be built. Since you don't know precisely how many "Itemxxx" and "Qtyxxx" columns, you'll need to do this dynamically, based on the values in the intermediate table show above.

Based on your example data, you want to have a blank in each column beyond the number of items sold by the current user. To do that, you need to check each value to see if it's NULL, and replace it with an empty string. Therefore, we need to create two lists of the columns to return: one that performs the check for NULL values, and one that's a simple list of the column names needed for the PIVOT table.

With these two column lists, we can build the actual PIVOT statement, and return the desired output.

Here's a script that does the job:

-- Commands to set up the environment

IF (OBJECT_ID('tempdb..#Test') IS NOT NULL) DROP TABLE #Test;
IF (OBJECT_ID('tempdb..#tmp') IS NOT NULL) DROP TABLE #tmp;

CREATE TABLE #Test ( username varchar(10)
                   ,itemsold varchar(100)
                   ,amtsold integer)

CREATE TABLE #tmp ( username varchar(10), [label] varchar(14)
                  ,[value] varchar(100), row_num int)

INSERT INTO #Test (username, itemsold, amtsold)
VALUES ('jj11', 'chair', 2)
      ,('jj11', 'doghouse', 3)
      ,('jj11', 'reindeer', 14)
      ,('ar44', 'bat', 1)
      ,('ar44', 'cap', 4)

-- Declare the variables to hold the dynamic SQL statement, and the two lists of columns

DECLARE @stmt nvarchar(max);
DECLARE @pivot_select_list nvarchar(max);
DECLARE @pivot_column_list nvarchar(max);

-- Turn the original data into a table with "Item" and "Qty" column names

WITH numbered AS
     (SELECT username, itemsold, SUM(amtsold) as amtsold
            ,ROW_NUMBER() OVER (PARTITION BY username ORDER BY itemsold) as row_num
        FROM #Test
       GROUP BY username, itemsold
INSERT INTO #tmp (username, [label], [value], row_num)
SELECT username
      ,'Item' + CAST(row_num as varchar(10)) as [label]
      ,itemsold as [value]
  FROM numbered
SELECT username
      ,'Qty' + CAST(row_num as varchar(10)) as [label]
      ,CAST(amtsold as varchar(10)) as [value]
  FROM numbered


-- Build the simple column list for the PIVOT statement

SET @pivot_column_list
         = STUFF((SELECT ',' + QUOTENAME([label]) 
                    from #tmp
                   group by row_num, [label]
                   order by row_num, [label]
                     FOR XML PATH(''), TYPE
                 ).value('.', 'NVARCHAR(MAX)') 

-- Build the list of columns for the SELECT list, checking for NULL values

SET @pivot_select_list
         = STUFF((SELECT ',ISNULL(' + QUOTENAME([label]) + ','''') as ' + QUOTENAME([label])
                    from #tmp
                   group by row_num, [label]
                   order by row_num, [label]
                     FOR XML PATH(''), TYPE
                 ).value('.', 'NVARCHAR(MAX)') 

-- Build the actual SQL PIVOT statement

SELECT @stmt = N'SELECT username, '
              +' FROM
       (SELECT username, [label], [value]
          FROM #tmp
       ) t
       (MAX([value]) FOR [label] IN (' + @pivot_column_list + ')
       ) p'

SELECT @pivot_column_list AS [column list];

-- Run the statement to get the desired output

EXECUTE sp_executesql @stmt;

Final output matches your desired output.

A couple of notes:

  • I've assumed that you want each item sold listed once and only once for each username; if one added another row to your sample data, ('jj11','chair',3), then the Qty1 for jj11 would be 5, not 2. If there should be two separate entries in this case, you'll need to modify the numbered CTE expression so it no longer aggregates that data.

  • Your data and output happens to list the items in alphabetical order. I've made that specific in the ROW_NUMBER() OVER () clause. If some other order I desired, you would need to provide a basis for it, and use that in the ORDER BY part of the OVER () clause.

FYI - there are a number of posts on SE covering dynamic SQL to do pivots - it's basically required if you don't know the precise columns ahead of time. I referred to this answer from stackoverflow.

| improve this answer | | | | |
  • 1
    I know we're not supposed to comment just to "+1" but wow, thanks. Dynamic Pivot? I'm glad I stumbled upon this. Definitely saving for when I need it. – MguerraTorres Nov 20 '17 at 22:21
  • 2
    @MguerraTorres - I should note that there are a number of dynamic pivot queries on the site already. If it wasn't for the need to pre-format the data, this could have probably been a dupe. – RDFozz Nov 20 '17 at 22:59
  • you're the humble hero we need but don't deserve. – MguerraTorres Nov 20 '17 at 23:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.