SQL Server Dynamic Pivot for an Unknow Number of Columns

SQL Server 2005 Pivot on Unknown Number of Columns

I know you said no dynamic SQL, but I don't see any way to do it in straight SQL.

If you check out my answers to similar problems at Pivot Table and Concatenate Columns and PIVOT in sql 2005

The dynamic SQL there is not vulnerable to injection, and there is no good reason to prohibit it. Another possibility (if the data is changing very infrequently) is to do code-generation - instead of dynamic SQL, the SQL is generated to a stored procedure on a regular basis.

SQL Server Pivot to unknown number of columns

I am just going to start with saying that this is going to be fun (and a bit evil).

Table structure:

CREATE TABLE #temp
(
id VARCHAR(100),
serialNo VARCHAR(100)
);

Test data

INSERT INTO #temp
VALUES
('0245DS6','05813542'),
('0245DS6','05813543'),
('0245DS6','05813544'),
('2231VC7','06885213'),
('5432PS1','01325131'),
('5432PS1','01325132')

Then get the unique groups:

DECLARE @columns VARCHAR(MAX)=
(
STUFF(
(
Select ','+QUOTENAME(CAST(rowId AS VARCHAR(100))) AS [text()]
FROM
(
SELECT DISTINCT
ROW_NUMBER() OVER(PARTITION BY id order by serialNo) AS rowId
FROM #temp
) as tbl
For XML PATH ('')
)
,1,1,'')
)

And then execute a dynamic pivot:

DECLARE @query NVARCHAR(MAX)='SELECT
*
FROM
(
SELECT
ROW_NUMBER() OVER(PARTITION BY id order by serialNo) as rowId,
id,
serialNo
FROM
#temp
)AS sourceTable
PIVOT
(
MAX(serialNo)
FOR rowId IN ('+@columns+')
) AS pvt'

EXECUTE sp_executesql @query

Result:

Id        1           2           3
-------------------------------------------
0245DS6 05813542 05813543 05813544
2231VC7 06885213 NULL NULL
5432PS1 01325131 01325132 NULL

SQL Server Dynamic pivot for an unknow number of columns

Try this, It follows the same example mentioned here:Convert Rows to columns using 'Pivot' in SQL Server

--Drop Sample temp Table     

IF OBJECT_ID('tempdb..#temp2') IS NOT NULL
BEGIN
DROP TABLE #temp2
END

--create Sample temp Table

create Table #temp2
(
[name] varchar(255),
Item varchar(255),
note varchar(255)
)

--Insert Sample Data

insert into #temp2
values( 'George','Paperclip','Two boxes'),
('George','Stapler','blue one'),
('George','Stapler','red one'),
('George','Desk lamp','No light bulb'),
('Mark','Paperclip','One box 2'),
('Mark','Paperclip','One box 4'),
('Mark','Block Notes','a blue one')

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

--Generate Columns from Data
--Generate Columns from Data

select @cols = STUFF((SELECT ', isnull(' + QUOTENAME(Item) + ',0) as' + QUOTENAME(Item)
from #temp2
group by Item
order by Item
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')

select @cols2 = STUFF((SELECT ', ' + QUOTENAME(Item)
from #temp2
group by Item
order by Item
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')

--Pivot Query
set @query = 'SELECT [name],' + @cols + ' from
(
select [Name], Item, count(*) as xcount
from #temp2
group by Name, Item
) x
pivot
(
sum(xCount)
for Item in (' + @cols2+ ')
) p '

execute(@query);

--Drop Sample Temp Table

IF OBJECT_ID('tempdb..#temp2') IS NOT NULL
BEGIN
DROP TABLE #temp2
END

How to pivot unknown number of columns & no aggregate in SQL Server?

Test Data

DECLARE @TABLE TABLE (loanid INT,name VARCHAR(20),[Address] VARCHAR(20))
INSERT INTO @TABLE VALUES
(1,'John','New York'),(1,'Carl','New York'),(1,'Henry','Boston'),
(2,'Robert','Chicago'),(3,'Joanne','LA'),(3,'Chris','LA')

Query

SELECT  loanid
,ISNULL(name1, '') AS name1
,ISNULL(Address1, '') AS Address1
,ISNULL(name2, '') AS name2
,ISNULL(Address2, '') AS Address2
,ISNULL(name3, '') AS name3
,ISNULL(Address3, '') AS Address3
FROM (
SELECT loanid
,'name' + CAST(ROW_NUMBER() OVER (PARTITION BY loanid ORDER BY loanid) AS NVARCHAR(10)) AS Cols
, name AS Vals
FROM @TABLE
UNION ALL
SELECT loanid
,'Address' + CAST(ROW_NUMBER() OVER (PARTITION BY loanid ORDER BY loanid) AS NVARCHAR(10))
, [Address]
FROM @TABLE ) t
PIVOT (MAX(Vals)
FOR Cols
IN (name1, Address1,name2,Address2,name3,Address3)
)P

Result Set

╔════════╦════════╦══════════╦═══════╦══════════╦═══════╦══════════╗
║ loanid ║ name1 ║ Address1 ║ name2 ║ Address2 ║ name3 ║ Address3 ║
╠════════╬════════╬══════════╬═══════╬══════════╬═══════╬══════════╣
║ 1 ║ John ║ New York ║ Carl ║ New York ║ Henry ║ Boston ║
║ 2 ║ Robert ║ Chicago ║ ║ ║ ║ ║
║ 3 ║ Joanne ║ LA ║ Chris ║ LA ║ ║ ║
╚════════╩════════╩══════════╩═══════╩══════════╩═══════╩══════════╝

Update for Dynamic Columns

DECLARE @Cols NVARCHAR(MAX);

SELECT @Cols = STUFF((
SELECT DISTINCT ', ' + QUOTENAME(Cols)
FROM (
SELECT loanid
,'name' + CAST(ROW_NUMBER() OVER (PARTITION BY loanid ORDER BY loanid) AS NVARCHAR(10)) AS Cols
, name AS Vals
FROM @TABLE
UNION ALL
SELECT loanid
,'Address' + CAST(ROW_NUMBER() OVER (PARTITION BY loanid ORDER BY loanid) AS NVARCHAR(10))
, [Address]
FROM @TABLE ) t
GROUP BY QUOTENAME(Cols)
FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'),1,2,'')

DECLARE @Sql NVARCHAR(MAX);

SET @Sql = 'SELECT ' + @Cols + '
FROM (
SELECT loanid
,''name'' + CAST(ROW_NUMBER() OVER
(PARTITION BY loanid ORDER BY loanid) AS NVARCHAR(10)) AS Cols
, name AS Vals
FROM @TABLE
UNION ALL
SELECT loanid
,''Address'' + CAST(ROW_NUMBER() OVER
(PARTITION BY loanid ORDER BY loanid) AS NVARCHAR(10))
, [Address]
FROM @TABLE ) t
PIVOT (MAX(Vals)
FOR Cols
IN (' + @Cols + ')
)P'

EXECUTE sp_executesql @Sql

Note

This wouldnt work with the given sample data in my answer, as it uses a table variable and it is not visible to dynamic sql since it has it own scope. but this solution will work on a normal sql server table.

Also the order in which columns are selected will be slightly different.

SQL pivot table for unknown number of columns

It´s possible, check this question

This other is a pivot that I did, also with an unknown number of columns, maybe it can help you too: Advanced convert rows to columns (pivot) in SQL Server

Simple SQL Pivot query with unknown number of columns

I prefer conditional aggregation:

select item,
max(case when seqnum = 1 then location end) as location_1,
max(case when seqnum = 2 then location end) as location_2,
max(case when seqnum = 3 then location end) as location_3
from (select t.*,
row_number() over (partition by loc order by loc) as seqnum
from t
) t
group by item;

Pivot Unknown Number Of Column Values

I guess you need something like this:

IF OBJECT_ID('tempdb..#DataSource') IS NOT NULL
BEGIN;
DROP TABLE #DataSource;
END;

CREATE TABLE #DataSource
(
[TableName] SYSNAME
,[FieldName] NVARCHAR(64)
,[LookupValues] NVARCHAR(64)
);

INSERT INTO #DataSource ([TableName], [FieldName], [LookupValues])
VALUES ('Pathology', 'Medical Report', 'Avaliable')
,('Pathology', 'Medical Report', 'Not Avaliable')
,('Pathology', 'Medical Report', 'Pending')
,('Pathology', 'Laboratory Report', 'Avaliable')
,('Pathology', 'Laboratory Report', 'Not Avaliable')
,('Pathology', 'Laboratory Report', 'Pending')
,('Pathology', 'Laboratory Report', 'Declined')
,('Pathology', 'Laboratory Report', 'Private')
,('Pathology', 'Laboratory Report', 'Rejected')
,('Oncology', 'Laboratory Report', 'Avaliable')
,('Oncology', 'Laboratory Report', 'Not Avaliable')
,('Oncology', 'Laboratory Report', 'Pending')
,('Morgue', 'Death Report', 'Type 1')
,('Morgue', 'Death Report', 'Type 2')
,('Morgue', 'Death Report', 'Type 3')
,('Morgue', 'Death Report', 'Type 4');

DECLARE @DynamicSQLStatement NVARCHAR(MAX)
,@PIVOTcolumns NVARCHAR(MAX);

SELECT @PIVOTcolumns = STUFF
(
(
SELECT DISTINCT ',[' + [FieldName] + ']'
FROM #DataSource
FOR XML PATH(''), TYPE
).value('.', 'nvarchar(max)')
,1
,1
,''
);

SET @DynamicSQLStatement = N'
SELECT *
FROM
(
SELECT *
,ROW_NUMBER() OVER (PARTITION BY [TableName], [FieldName] ORDER BY [LookupValues]) AS [RowID]
FROM #DataSource
) DS
PIVOT
(
MAX([LookupValues]) FOR [FieldName] IN (' + @PIVOTcolumns + ')
) PVT
ORDER BY [TableName];'

EXEC sp_executesql @DynamicSQLStatement;

Sample Image

Of course, with your sample data, the result will be:

Sample Image

I am sure you can use the code to solve your issue. There is nothing complicated - you need only to build a string with the values on which PIVOT is performed.

Pivot rows into unknown number of columns

You can use conditional aggregation:

select subno,
sum(case when offer = 'offer1' then 1 else 0 end) as offer1,
sum(case when offer = 'offer2' then 1 else 0 end) as offer2,
sum(case when offer = 'offer3' then 1 else 0 end) as offer3
from t
group by subno;


Related Topics



Leave a reply



Submit