SQL Server pivot table to show as a row

Question

I am using SQL Server. I created a query that shows the data as:

    accountNumber  fundid
--------------------------
1   238492348       1004
2   238492348       1005
3   238492348       1006
4   238492348       1007
5   238492348       1008
6   238492348       1009
7   238492348       1022
8   238492348       1339

I am trying to some how pivot the table in order to make the table look like this instead:

   accountNumber  adv1  adv2  adv3  adv4  adv5  adv6  adv7  adv8
-----------------------------------------------------------------
1   238492348     1004  1005  1006  1007  1008  1009  1022  1339

Can someone assist me in how I can do this with SQL Server?

Everything I am reading wants me to do like a sum of the numbers or find an aggregate but I am just trying to rotate it and turn it into one row. The most columns it should ever have is up to adv25.

Any help would be greatly appreciated.

In pivot command you always have to implement an aggregate function, even if there are not records to group — DarioN1, Dec 14 '18 at 16:27

score 1 · Accepted Answer · answered Dec 14 '18 at 16:41

This should get you started:

--DROP TABLE #tmpS
CREATE TABLE #tmpS (accountNumber VARCHAR(25), fundid int)
INSERT INTO #tmpS SELECT '238492348',1004
INSERT INTO #tmpS SELECT '238492348',1005
INSERT INTO #tmpS SELECT '238492348',1006
INSERT INTO #tmpS SELECT '238492348',1007
INSERT INTO #tmpS SELECT '238492348',1008
INSERT INTO #tmpS SELECT '238492348',1009
INSERT INTO #tmpS SELECT '238492348',1022
INSERT INTO #tmpS SELECT '238492348',1339

-- Consider adding more data here:
INSERT INTO #tmpS SELECT '240000000',1337
INSERT INTO #tmpS SELECT '240000000',1337


SELECT *
FROM
   (SELECT ColNbr = ROW_NUMBER() OVER(PARTITION BY accountNUmber ORDER BY fundid,accountNumber)
        ,accountNumber
        ,fundid
    FROM
      #tmpS a
   ) src PIVOT(MAX(src.fundid) FOR src.ColNbr IN(       [1]
                                           ,[2]
                                           ,[3]
                                           ,[4]
                                           ,[5]
                                           ,[6]
                                           ,[7]
                                           ,[8])) pvt

I'm here -- can you post the other query in this thread? Or start another question with it? I'll take a look — Vic Street, Dec 14 '18 at 18:33
I'm not familiar with the chat feature - can you post the code here — Vic Street, Dec 14 '18 at 18:38
I see the problem - you need dynamic columns. There's a similar question [here](https://stackoverflow.com/questions/11985796/pivot-dynamic-columns-no-aggregation). I'll try to write it for your application in 1 min — Vic Street, Dec 14 '18 at 18:46
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/185265/discussion-between-vic-street-and-david-brierton). — Vic Street, Dec 14 '18 at 19:01

SQL Server pivot table to show as a row

1 Answers1