SQL Server 2008: need "transposed"resultset

Question

I am having trouble creating a query. I have the following tables (simplified):

table1:

doc_no         line         country
12             1            NL
12             2            US
12             ...          ...
12             m            FR
13             1            NL
13             ...          ...
13             n            GR 
...            ...          ...

table2:

doc_no         user_doc
12             123456
13             654321

I need to create a query that joins both tables on doc_no and returns the following result set(s):

user_doc       country-1     country-2    country-...    country-m
123456         NL            US           ...            FR

user_doc       country-1     country-...  country-n
654321         NL            ...          GR

user_doc       country-...
...            ...

There are no set values for m, n or the number of doc_no's in both tables. In the end, the resultset will be limited be the interval in which the query will run (likely to be 30 or 60 seconds) so the result set can be 10's of doc_no's. Most likely is that the result set will be ranging from 1 to 5 user_doc's, but more is possible.

The database allows data-extraction only, so I cannot create temp tables, stored procedures, views etc. only select-type queries using local variables etc using T-SQL.

I am completely lost for solutions, so any suggestion would be wonderful

Thx,

Martin

It is possible to use `pivot` but it only works on a predefined list of values (so countries are `nl, en, fr`, not just any). Is that what you want? — Patrick Hofman, Feb 13 '14 at 13:00
unfortunately not a predefined list of values. I guess that rules out pivot? — MartinB, Feb 13 '14 at 14:56
Yes. The solution of @StuartLC is also based on using pivots, but he uses a function to get them dynamically. If that is possible to you, use that. — Patrick Hofman, Feb 13 '14 at 15:01

score 0 · Accepted Answer · edited May 23 '17 at 12:13

This can be done with a modification to @Bluefeet's dynamic pivot strategy, and using dynamic Sql. I'm assuming the 'Country-X' headers use the value of column line as x:

DECLARE 
  @pivotCols AS NVARCHAR(MAX),
  @query  AS NVARCHAR(MAX);

-- Grab the distinct Lines to use as column headings 
-- (This is a `GROUP_CONCAT` workaround)
SET @pivotCols = STUFF((SELECT distinct ',' + 
       QUOTENAME('Country-' + CAST(Line AS NVARCHAR(20))) 
            FROM table1
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');

set @query = '
    SELECT user_doc, ' + @pivotCols + '
    FROM
    (
        select ''Country-''+CAST(t1.Line AS NVARCHAR(20)) AS Line,  
                    t1.Country, t2.user_doc
        from table1 t1
            inner join table2 t2 on t1.doc_no = t2.doc_no
     ) x
     pivot 
    (
       MIN(Country)
       for Line in (' + @pivotCols + ')
    ) p';

execute(@query);

If there is more than one country per doc_no + line combination, the lowest is picked. Nulls are displayed if there is no such line.

SqlFiddle here

Edit

The below uses a ROW_NUMBER() to add an arbitrary counter instead of relying on Line (change the ORDER BY as needed). I've used a nasty #temp table hack (because #Temp tables are also accessible to EXEC'ed queries) to DRY up the naming of the columns.

DECLARE 
  @pivotCols AS NVARCHAR(MAX),
  @aliasCols AS NVARCHAR(MAX),
  @query  AS NVARCHAR(MAX);

select 'Country-'+CAST(y.TheNumber AS NVARCHAR(20)) AS ColName, 
           y.Country,
           y.user_doc
    into #temp
    from 
    ( 
        SELECT 
           t1.Country, t2.user_doc, 
           ROW_NUMBER() OVER (PARTITION BY t1.doc_no ORDER BY LINE ASC) AS TheNumber
        FROM 
           table1 t1
           inner join table2 t2 on t1.doc_no = t2.doc_no
     ) y;

SET @pivotCols = STUFF((SELECT distinct ',' + QUOTENAME(ColName)
            FROM #temp
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');

set @query = '
    SELECT user_doc, ' + @pivotCols + '
    FROM
    #temp x
    pivot 
    (
       MIN(Country)
       for ColName in (' + @pivotCols + ')
    ) p';

execute(@query);

Updated SqlFiddle here

Hi Stuart, thanks for your quick answer. The line nr are not exactly related to the country-x headers. examples of line-nrs can also be 010, 020, 030 or 1a,1b,2a,2b etc. so pretty much "user-entry". in the output, the result should always be country-1, country-2 etc. How does that change your solution? — MartinB, Feb 13 '14 at 14:58
Hi Stuart,This is great. the only thing I am still looking for and having trouble to create myself is to have separate result sets for lines with different counts of "Country". So instead of displaying "nulls" if count of country per line is less than the max count, return a separate result set. Any tips? — MartinB, Feb 21 '14 at 07:59

SQL Server 2008: need "transposed"resultset

1 Answers1