SQL Pivot Select from List (In Select)

SQL PIVOT SELECT FROM LIST (IN SELECT)

If dynamic SQL is out then I'm afraid the answer is no, it can't be done. The parser needs to know the values up front to perform the pivot to columns.

Querying pivot table to return a list of referred rows

Here's a solution to your riddle:

SELECT DISTINCT ON (pg.id, p.prod_id)
       pg.group_name, p.name AS prod_name, v.version
     , COALESCE((SELECT default_something FROM version_child WHERE version_id = v.id)
              , (SELECT default_something FROM product_child WHERE prod_id = p.prod_id)
              , (SELECT default_something FROM product_group_child WHERE group_id = pg.id)
               ) AS something
     , ARRAY(SELECT p1.name
             FROM   pivot pv
             JOIN   product p1 USING (prod_id)
             WHERE  pv.version_id = v.id
            ) AS ref
FROM   product_group pg
LEFT   JOIN product  p ON pg.id = p.group_id
LEFT   JOIN version  v ON v.prod_id = p.prod_id
ORDER  BY pg.id, p.prod_id, v.version DESC;

(Rather than an answer to a question.)

db<>fiddle here

Related:

• Why is array_agg() slower than the non-aggregate ARRAY() constructor?

Alternatively, you could use a LATERAL subquery:

• What is the difference between LATERAL JOIN and a subquery in PostgreSQL?

how to add select statement in IN clause in pivot

use this i got answer from some of google posts

select @cols = 
stuff( ( select distinct  ',[' + Ltrim(rtrim(Ds.CollectionName)) +']' from v_DeploymentSummary Ds
left join v_AuthListInfo LI on LI.ModelID = Ds.ModelID

Where Ds.FeatureType = 5 and Ds.CollectionName  not like '%Windows 8%'

and Li.Title like  @SUGname  FOR XML PATH('')),1,1,'');

T sql pivot table selecting for IN

You have to use DYNAMIC PIVOT something like this:

DECLARE @cols   AS NVARCHAR(MAX) = '',
        @sql    AS NVARCHAR(MAX)

SELECT @cols += N'' + QUOTENAME(ProductID) + ', '
FROM   (
        SELECT DISTINCT ProductID
        FROM Product                         
        ) a                         
SET @cols   = LEFT(@cols, LEN(@cols) - 1)
SET @sql    = N'SELECT * FROM 
                (
                    SELECT 
                        year(createdDate) as [year],month(createdDate) as [month],cp.product_Id as product_ID, 
                        cp.salesprice as Amount 
                    FROM customer_products cp               
                ) x 
                PIVOT
                (
                    SUM(Amount)
                    FOR [product_Id] IN (' + @cols + ') 
                ) p

EXEC Sp_executesql @sql

SQL Server pivot query - questions

Based on @seanb answer that saved me, I tried to replace the NULL values with 0. I understood the principle (the base). Here is how I updated the SQL request to replace the NULL values.

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX),
        @PivotColumnNames AS NVARCHAR(MAX),
        @PivotSelectColumnNames AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column
SELECT @PivotColumnNames= ISNULL(@PivotColumnNames + ',','') + QUOTENAME(prod_id)
FROM (SELECT DISTINCT prod_id FROM #MyTable) AS prod_id

--Get distinct values of the PIVOT Column with isnull
SELECT @PivotSelectColumnNames 
    = ISNULL(@PivotSelectColumnNames + ',','')
    + 'ISNULL(' + QUOTENAME(prod_id) + ', 0) AS '
    + QUOTENAME(prod_id)
FROM (SELECT DISTINCT prod_id FROM #MyTable) AS prod_id

--Prepare the PIVOT query using the dynamic
SET @DynamicPivotQuery =
N'SELECT order_id, ' + @PivotSelectColumnNames + '
FROM #MyTable
PIVOT(SUM(product_count)
FOR prod_id IN (' + @PivotColumnNames + ')) AS PivotTable'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

SQL query with PIVOT

Put the name of the student in the subquery

FROM
(
SELECT Student, Grades, Subject FROM Grade_Report
)AS SourceTable

SELECT Student, [English], [Mathematics], [Science], [Programming], 
[History]
FROM
(
    SELECT Student, Grades, Subject FROM Grade_Report
)AS SourceTable
PIVOT
(
    AVG(Grades)
    FOR Subject IN([English], [Mathematics], [Science], [Programming], 
    [History])
)AS PivotTable;

How to write select query in in clause of pivot table?

Declare @cols nvarchar(max)
select @cols = 
stuff( ( select distinct  ',[' + Ltrim(rtrim(Software)) +']' from temp FOR XML PATH('')),1,1,'');

EXEC('select * from temp pivot(count(Software) for Software in ('+@cols+')) as PVT')  

The @cols variable will contain the rows fetched from the query select distinct Software from temp as XML format: that is [Standard],[Personal],[Professional] and then the result is sent to the pivot query statement using EXEC() function.

SQL Pivot select multiple rows

Use max in time of selection like below:

select 
max([1]) as Client0, 
max([2]) as Client1, 
max([3]) as Client2, 
max([4]) as Client3,
max([5]) as Client4
from 
(
select 
    rc.DateCreated, 
    gd.Name,
    DENSE_RANK() over (order by gd.ID_TableGD) as colnum
from TableGD gd
inner join TableRC rc ON gd.ID_TableGD = rc.ID_TableRC
WHERE gd.ID_TableGD IN (962,1029,1024) 
AND gd.Active = 1
) as t
pivot
(
MAX(Name)
for colnum in 
(
[1],
[2],
[3],
[4],
[5]

)
) as pvt

T-SQL :: List all tables, columns and pivot content

Updated to support 2016

DROP TABLE IF EXISTS #ColumnsToDisplay

SELECT    ROW_NUMBER () OVER (ORDER BY tab.name) AS Iteration,
          s.name AS SchemaName,
          tab.name AS table_name,
          col.column_id,
          col.name AS column_name,
          t.name AS data_type,
          col.max_length,
          col.precision AS PrecisionNumber,
          CAST(NULL AS VARCHAR(MAX)) AS DataSample
INTO      #ColumnsToDisplay
FROM      sys.tables AS tab
JOIN      sys.schemas AS s
    ON    s.schema_id = tab.schema_id
JOIN      sys.columns AS col
    ON    col.object_id = tab.object_id
LEFT JOIN sys.types AS t
    ON    col.user_type_id = t.user_type_id
     
DECLARE @Iterations       INT = 0,
        @CurrentIteration INT = 1;

SELECT @Iterations = MAX (Iteration)
FROM   #ColumnsToDisplay

WHILE @CurrentIteration <= @Iterations
BEGIN
    DECLARE @CurrentTableName  VARCHAR(100)   = '',
            @CurrentColumnName VARCHAR(100)   = '',
            @DynamicQuery      NVARCHAR(1000) = N''
    DECLARE @Sample VARCHAR(MAX)

    SET @CurrentTableName = '';
    SET @DynamicQuery = N'';
    SELECT @CurrentTableName = CONCAT (ttq.SchemaName, '.', ttq.table_name),
           @CurrentColumnName = ttq.column_name
    FROM   #ColumnsToDisplay AS ttq
    WHERE  ttq.Iteration = @CurrentIteration

    IF (@CurrentTableName = '')
    BEGIN
        SET @CurrentIteration += 1

        CONTINUE
    END

 -- SQL Server 2019
 -- SET @DynamicQuery = CONCAT (N'
 --       SELECT @Sample = STRING_AGG(t.ColumnData,'', '') 
 --       FROM (
 --               SELECT TOP 5  CAST(x.[', @CurrentColumnName, '] AS VARCHAR(MAX)) AS ColumnData 
 --               FROM ', @CurrentTableName, ' AS x 
 --               WHERE x.[', @CurrentColumnName, '] IS NOT NULL
 --       )t')

 -- SQL Server 2016 and lower where Stuff is supported   
    SET @DynamicQuery = CONCAT (N'
    SELECT @Sample =  STUFF((SELECT '', ''+ t.ColumnData  
    FROM (
            SELECT TOP 5 CAST(x.[', @CurrentColumnName, '] AS VARCHAR(MAX)) AS ColumnData 
            FROM ', @CurrentTableName, ' AS x 
            WHERE x.[', @CurrentColumnName, '] IS NOT NULL 
    ) AS t
       FOR XML PATH('''')),1,1,'''')')
        
    EXECUTE sys.sp_executesql @DynamicQuery,
                              N'@Sample VARCHAR(MAX) OUTPUT',
                              @Sample = @Sample OUTPUT

    UPDATE #ColumnsToDisplay
    SET    DataSample = @Sample
    WHERE  Iteration = @CurrentIteration

    SET @CurrentIteration += 1
END

SELECT ctd.Iteration,
       ctd.SchemaName,
       ctd.table_name,
       ctd.column_id,
       ctd.column_name,
       ctd.data_type,
       ctd.max_length,
       ctd.PrecisionNumber,
       ctd.DataSample
FROM   #ColumnsToDisplay AS ctd 

---

Related Topics