Dynamic convert rows to columns in sql server whitout aggregate

Question

Hi,

I have this data:

---------------------------------
|Masterarticle | childarticle   |
----------------------------------
|     12341       |   12345        |
| 12341     |   12346    |
| 12341       | 12347    |
| 12342       | 44875    |
|     12342 | 44876    |
----------------------------------

and this is my expected resultset:

|   12341   |    12342   |
-------------------------
|   12345   |    44875   |
-------------------------
| 12346 |    44876 |
-------------------------
| 12347 |    NULL |
-------------------------
| 12347 |    NULL |
-------------------------

I have this, but it shows me only one row, because of the aggregate max(childarticle). Is there any way, to query a dynamic table whitout aggregate?

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

select @cols = STUFF((SELECT ',' + QUOTENAME(Masterarticle) 
                    from  table 
                    group by Masterarticle

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

set @query = N'SELECT ' + @cols + N' from 
             (
                select childarticle, Masterarticle
                from table 
            ) x
            pivot 
            (
                max(childarticle)
                for Masterarticle in (' + @cols + N')
            ) p '

exec sp_executesql @query;

So, what is your expected resultset here? Are those 2 sets of data both in your table? — Thom A
– Thom A ♦, Commented Mar 27, 2018 at 9:53

LukStorms · Accepted Answer · 2018-03-27 12:00:37Z

1

All you need to do is add a row_number to the query used for the pivot.

Then the PIVOT will group on that calculated row_number and return more that 1 row.

An example using a temporary table that only lives in the session:

IF OBJECT_ID('tempdb..#tmpTestTable') IS NOT NULL DROP TABLE #tmpTestTable;
create table #tmpTestTable (Masterarticle int, Childarticle int);

insert into #tmpTestTable (Masterarticle, Childarticle) values
(12341, 12345),(12341, 12346),(12341, 12347),
(12342, 44875),(12342, 44876);

DECLARE @cols AS NVARCHAR(MAX) = STUFF((SELECT ',' + QUOTENAME(Masterarticle) from  #tmpTestTable group by Masterarticle FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'');
--select @cols;

DECLARE @query AS NVARCHAR(MAX) = N'SELECT ' + @cols + N'
 from (
    select Masterarticle, Childarticle,
    row_number() over (partition by Masterarticle order by Childarticle) as RN
    from #tmpTestTable
) x
pivot (max(childarticle) for Masterarticle in (' + @cols + N')) pvt';

exec sp_executesql @query;

Returns:

12341  12342
-----  -----
12345  44875
12346  44876
12347  NULL

edited Mar 27, 2018 at 12:00

answered Mar 27, 2018 at 11:46

LukStorms

29.8k5 gold badges36 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Batchechooff Over a year ago

ok, i can not modify the sql table. But everything from DECLARE works perfect! Many Thanks!

LukStorms Over a year ago

@Batchechooff Not sure why you would need to modify the sql table. I merely used a temporary table for demonstration purposes. Since that's easier for others to verify that it works. :)

Collectives™ on Stack Overflow

Dynamic convert rows to columns in sql server whitout aggregate

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related