A help In the Pivot Method

Question

Help In the pivot method , i dont want to keep writing all the Id is there a way to Like Do it without writing all the id

I dont know how

SELECT  'Montant' AS IdClient,
*
FROM
(
SELECT   MontantTransaction,IdClient
FROM   Transactions
) AS TableSource
PIVOT
(
Sum(MontantTransaction)
FOR IdClient IN( [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24])
) AS TableDePivot;

i expect to see this code without the ( [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13]....

have a look https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query — Mukesh Arora, Jul 08 '19 at 14:03
Will you limit your Ids somehow? There's a limit for the amount of columns that SQL Server can return. — Luis Cazares, Jul 08 '19 at 14:03
So your profile pic contains an offensive word - and you don't think that is a problem? — SMor, Jul 08 '19 at 14:11

Mukesh Arora · Accepted Answer · 2019-07-08T14:28:54.033

Try this -

Sample Temp Table & Data

Create Table #Transactions 
(

    IdClient varchar(3),
    MontantTransaction decimal(10,2)
)

insert into #Transactions values (1, 1000.00)
insert into #Transactions values (1, 200.00)
insert into #Transactions values (2, 800.00)
insert into #Transactions values (2, 700.00)
insert into #Transactions values (3, 1100.00)
insert into #Transactions values (4, 1400.00)

Query

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

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.IdClient) 
            FROM #Transactions c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT ''Montant'' AS IdClient, ' + @cols + ' from 
            (
                select 
                    MontantTransaction
                    , IdClient
                from #Transactions
           ) x
            pivot 
            (
                 Sum(MontantTransaction)
                for IdClient in (' + @cols + ')
            ) p '


execute(@query)

drop table #Transactions

can u please explain for me what does SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(NomClient) FROM Client FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') — Younes Harir, Jul 08 '19 at 15:00
This will return [1],[2],[3],[4] based on sample table data and which will be used to generate a dynamic pivot table. you can print @cols to see the result. — Mukesh Arora, Jul 09 '19 at 04:07

A help In the Pivot Method

1 Answers1