Many datasets into one row and multiple columns with count (T-SQL)

Question

This is the result of my sql statement:

   shopId  transactionId  articleId
   100     8797           4711
   100     8797           3572
   100     8797           3572
   100     8797           3001

I need to merge the rows by the transactionId into one row. Also I need to 'create' for every article a new column, like this:

   shopId  transactionId  article1 article1Count article2 article2Count article3 article3Count 
   100     8797           4711     1             3572     2             3001     1

What is the proper way to performe this task dynamic with T-SQL?

Thanks in advance!

Look for dynamic SQL Server Pivot answers, this has been answered many times, for instance here : https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query — Søren Kongstad, Jul 05 '19 at 09:44
@Tomato32 1 is correct because the article "3001" appears only one time in the transaction 8797. — Nuls, Jul 05 '19 at 10:47

score 1 · Accepted Answer · answered Jul 05 '19 at 13:09

Sorry for the late response.

Here is a solution. I hope it's useful to you, my friend :))

--Create a sample data
create table temp
(
    shopid int,
    transactionId int,
    articleId int,
)

create table yt
(
    shopid int,
    transactionId int,
    article int,
    articleCount int
)

insert into temp values (100, 8797, 4711)
insert into temp values (100, 8797, 3572)
insert into temp values (100, 8797, 3572)
insert into temp values (100, 8797, 3001)

insert into yt
select shopid, transactionId, articleId, count(articleId) as articleCount
from temp 
group by  shopid, transactionId, articleId

------

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

select @cols = STUFF((SELECT ',' + QUOTENAME(c.col+cast(rn as varchar(10))) 
                    from 
                    (
                      select row_number() over(partition by shopid 
                                               order by shopid, article) rn
                      from yt
                    ) d
                    cross apply
                    (
                      select 'article' col, 1 sort union all select 'articleCount', 2
                    ) c
                    group by col, rn, sort
                    order by rn, sort
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

        print @cols

set @query = 'SELECT shopid, ' + @cols + '
              from
              (
                select shopid,
                  col+cast(rn as varchar(10)) col,
                  value
                from
                (
                 -- when you perform an unpivot the datatypes have to be the same. 
                 -- you might have to cast the datatypes in this query
                  select shopid, article, cast(articleCount as int) as articleCount,
                    row_number() over(partition by shopid order by shopid, article) rn
                  from yt
                ) src
                unpivot
                (
                  value
                  for col in (article, articleCount)
                ) unpiv
              ) d
              pivot 
              (
                  max(value)
                  for col in (' + @cols + ')
              ) p '

execute(@query);

---Delete table
drop table temp
drop table yt

Many datasets into one row and multiple columns with count (T-SQL)

1 Answers1