How to pivot without knowing fixed columns in T-SQL

Question

I have a table called balance which I wish to pivot, however it is quite difficult since the column names would be labelled 1,2,3 and balances would be sorted by descending order per customer.

This is screenshot of current table:

And the pivot table:

I reviewed other videos and post but I didn't find a solution to match my current situation what I want to achieve. So the final results would be the customer would be sorted by asc and balances would be sorted by desc. So for customer 3 the highest balance of 500 would be placed in column 1, 300 in column 2 and in 250 in column 3.

Script to create sample data:

select Customer, Balance
into #a
from (
values
  (1,     250), 
  (2,     500), 
  (1,     205), 
  (2,     600), 
  (2,     700),
  (3,     300),
  (3,     500),
  (3,     250)
) v (Customer, Balance)

FYI: SSMS isn't a RDBMS its a client interface to SQL Server. — Dale K, Feb 21 '20 at 01:53
Short answer: You can't. T-SQL does not support runtime-defined or parameterised identifiers (like column names). You'll have to either use Dynamic-SQL (please don't) or perform the PIVOT in your application code. Only use Dynamic SQL with `PIVOT`/`UNPIVOT` if you need to process the pivoted data inside SQL Server. — Dai, Feb 21 '20 at 01:54
ok @Dai. I know I have to use dynamic SQL in this situation. My problem is how would I pass values in descending order to the column listing [1], [2], [3]? and if for example customer 2, there was only 2 entries then 0 would be placed in column 3. — JasonX, Feb 21 '20 at 02:02

Nguyễn Văn Phong · Accepted Answer · 2020-02-22T02:18:44.030

3

Demo on db<>fiddle

You can use ROW_NUMBER() to mark the number of values, e.g: 1, 2, 3.

Note that: ORDER BY [Balance] DESC to get the generated value as you wish.

DECLARE 
    @columns NVARCHAR(MAX) = '',
    @sql     NVARCHAR(MAX) = '';


 SELECT Customer, Balance, Col = ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY [Balance] DESC)
 into #b
 FROM #a

SELECT @columns += QUOTENAME(Col) + ','
from (SELECT DISTINCT Col FROM #b) A

-- remove the last comma
SET @columns = LEFT(@columns, LEN(@columns) - 1);


SET @sql = 'SELECT * FROM ( SELECT Customer, Balance, Col FROM  #b) src PIVOT( MAX([Balance]) FOR Col IN ('+ @columns +')) AS pivot_table;';

-- execute the dynamic SQL
EXECUTE sp_executesql @sql;

Output

Updated

Since concatenating strings is undocumented and unreliable. It does not always work as expected. So you should resolve with 2 solutions below

Use STRING_AGG (From SQL Server 2017 and late)

SELECT STRING_AGG(QUOTENAME(Col), ', ')
from (SELECT DISTINCT Col FROM #b) A
// Output: [1], [2], [3]

Use XML Extensions

DECLARE  @columns NVARCHAR(MAX) = ''
SELECT @columns = (
  SELECT QUOTENAME(Col) + ', '
  FROM (SELECT DISTINCT Col FROM #b) A

  FOR XML PATH(''), TYPE
                   ).value('.','varchar(max)')
SELECT @columns 
// Output: [1], [2], [3],

Thanks @GarethD's comment. Check it out on db<>fiddle

edited Feb 22 '20 at 02:18

answered Feb 21 '20 at 02:01

Nguyễn Văn Phong

13,506
17
39
56

1

Using variable assigment to concatenate strings (`SELECT @Column += ....`) is [undocumented and unreliable. It does not always work as expected](https://marc.durdin.net/2015/07/concatenating-strings-in-sql-server-or-undefined-behaviour-by-design/). You should either use `STRING_AGG` (if using a version that supports it), or use [XML Extensions](https://stackoverflow.com/a/5031297/1048425) to concatenate the rows. – GarethD Feb 21 '20 at 16:08
Thanks for your comment. I'll update with your advice soon. Because I'm online on my phone. – Nguyễn Văn Phong Feb 21 '20 at 16:10
GarethD. Wow. Your pretty knowledgable. I sourced the problem to this line (SELECT @Column += ....). but I just could not figure out how to adjust it to achieve what I really wanted. You figured it out very nicely. phong when I used my source data upon execution the columns would been [3], [1], [2]. And I really wanted it to be [1], [2], [3] once procedure was executed. – JasonX Feb 21 '20 at 16:21
I've just updated my answer, please take a look at. Thanks to your advice @GarethD – Nguyễn Văn Phong Feb 22 '20 at 02:19
@JasonX I've just updated my answer. Please take a look at and give me your feedback. – Nguyễn Văn Phong Feb 23 '20 at 00:45

score 0 · Answer 2 · edited Feb 24 '20 at 11:50

0

You can try this way

select *,concat('Price',RANK() OVER (PARTITION BY i.REFERENCE_NO ORDER BY i.TASK_ID 
 DESC)) AS Rank  into #temp from [dbo].[Table_1] i

 select REFERENCE_NO,Price1,Price2,Price3 from 
   (
    select REFERENCE_NO,TASK_ID,Rank from #temp
    ) as cte 
    PIVOT (SUM(TASK_ID)
    FOR rank IN (Price1,Price2,Price3 ))
    as PVT

edited Feb 24 '20 at 11:50

Nguyễn Văn Phong

13,506
17
39
56

answered Feb 24 '20 at 07:39

Bhupendra kumar

5
5

score 0 · Answer 3 · answered Feb 24 '20 at 08:52

Try this.

declare @balanceAMTCols varchar(max)='',
@statement nvarchar(4000)=''

 select @balanceAMTCols= stuff( 
            (
                select ','+ QUOTENAME(balanceamtcol) from (
             select  distinct   convert(varchar(10),(row_number() over(partition by Customer order by Customer))) 
            balanceamtcol FROM #a   ) as tbl
            for xml path('')),1,1,'')

set @statement= 'select Customer,'+@balanceAMTCols+' from (
    select  Customer,Balance,convert(varchar(10),(row_number() over(partition by Customer order by Customer))) as balanceamtcol  FROM #a  
) [CustomerTbl]
pivot
(
    max(Balance)
    for balanceamtcol
    in ('+@balanceAMTCols+') ) as pivatetble'

exec sp_executesql  @statement

How to pivot without knowing fixed columns in T-SQL

3 Answers3

Linked

Related