SQL Server 2005 Pivot on Unknown 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 perhaps?

You can use a PIVOT clause. Your query could be something like this:

WITH Source as (
SELECT Name1, Name2, [Value]
FROM mytable
)

SELECT Name2, CASE WHEN A IS NOT NULL THEN A ELSE 'your string' END As A
, CASE WHEN B IS NOT NULL THEN B ELSE 'your string' END As B
FROM (
SELECT Name2, Name1, [Value]
FROM Source
) s
PIVOT
(
MAX([Value]) FOR Name1 IN (A, B) -- any other Name1 would go here
) p

using your sample data above, my results were

P1  1           3
P2 1 1
P3 2 your string
P4 your string 1

EDIT:

Since you have an unknown number of columns, you will need to look at using dynamic SQL and there are several answers here on SO about that with PIVOT.

SQL Server 2005 Pivot on Unknown Number of Columns

Pivot Table and Concatenate Columns

SSRS Report from a PIVOT query with unknown number of columns

SSRS will not be able to handle your pivot table because the columns are not known at design time. SSRS has feature called Matrix that will handle the pivot a run time based off run time data if your design time structure is static.

ID | ColumnName | Value
------------------------
1 | Colu1 | Value1
1 | Colu2 | Value2
2 | Colu2 | Value2

I would recommend that you return detail rows from TSQL and allow SSRS to handle the pivot. This will allow for one stored procedure to be used for multiple SSRS reports and aggregations instead of the single aggregation provide by PIVOT.

To save on duplicate processing, Matrix will also handle the sorting so no need to sort inside of TSQL.

Reference: Create a Matrix

SQL Server dynamic PIVOT query?

Dynamic SQL PIVOT:

create table temp
(
date datetime,
category varchar(3),
amount money
)

insert into temp values ('1/1/2012', 'ABC', 1000.00)
insert into temp values ('2/1/2012', 'DEF', 500.00)
insert into temp values ('2/1/2012', 'GHI', 800.00)
insert into temp values ('2/10/2012', 'DEF', 700.00)
insert into temp values ('3/1/2012', 'ABC', 1100.00)


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

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

set @query = 'SELECT date, ' + @cols + ' from
(
select date
, amount
, category
from temp
) x
pivot
(
max(amount)
for category in (' + @cols + ')
) p '


execute(@query)

drop table temp

Results:

Date                        ABC         DEF    GHI
2012-01-01 00:00:00.000 1000.00 NULL NULL
2012-02-01 00:00:00.000 NULL 500.00 800.00
2012-02-10 00:00:00.000 NULL 700.00 NULL
2012-03-01 00:00:00.000 1100.00 NULL NULL

Is it possible that we pass a query to PIVOT as column list in SQL Server?

The IN list defines the resultset layout.

It should be known at parse time.

Can I Group By an Unknown Number of Columns?

A partial answer, and not an ideal one:
If you know that there will never be more than say 20 dye combinations, you can create another temp table with

select b.thread_group, 
case when d.sequence=1 then d.dye_code end as code1,
case when d.sequence=1 then d.dye_conc end as conc1,
case when d.sequence=2 then d.dye_code end as code2,
case when d.sequence=2 then d.dye_conc end as conc2,
case when d.sequence=3 then d.dye_code end as code3,
case when d.sequence=3 then d.dye_conc end as conc3,

case when d.sequence=20 then d.dye_code end as code20,
case when d.sequence=20 then d.dye_conc end as conc20
from #t_batch t, #t_batch_dye d
where t.batch_id = d.batch_id

and then select your group out of that, using all of code1 to conc20. It's not beautiful, but it's clear. And I know it negates the whole point of normalising your tables out in the first place! Good luck.

2005 SSRS/SQL Server PIVOT results need reversing

I was able to produce the results you were looking for by adding in a number (RowNum) to the query underneath the PIVOT operator. It doesn't have to be in the final query (though you might want it for client-side sorting), but by having it in the underlying layer the PIVOT operation treats that number like a member of a GROUP BY clause.

Please look through my sample SQL below and let me know if this matches your criteria.

CREATE TABLE #TMP
(
Name VARCHAR(10),
Test VARCHAR(20),
EffectiveDate DATETIME
)
INSERT INTO #TMP (Name, Test, EffectiveDate)
SELECT 'Jane', 'NM_Course1', '01/17/2014' UNION
SELECT 'Jane', 'NMEP_Course1', '12/19/2013' UNION
SELECT 'Jane', 'NMEP_Course1', '12/20/2013' UNION
SELECT 'Jane', 'NMEP_Course2', '12/19/2013' UNION
SELECT 'Jane', 'NMEP_Course2', '12/22/2013' UNION
SELECT 'Jane', 'NMEP_Course2', '01/05/2014' UNION
SELECT 'John', 'NM_Course1', '01/17/2014' UNION
SELECT 'John', 'NMEP_Course1', '01/11/2014'



DECLARE @sql AS varchar(max)
DECLARE @pivot_list AS varchar(max) -- Leave NULL for COALESCE technique
DECLARE @select_list AS varchar(max) -- Leave NULL for COALESCE technique

SELECT @pivot_list = COALESCE(@pivot_list + ', ', '') + '[' + CONVERT(varchar, PIVOT_CODE) + ']'
,@select_list = COALESCE(@select_list + ', ', '') + '[' + CONVERT(varchar, PIVOT_CODE) + '] AS [col_' + CONVERT(varchar, PIVOT_CODE) + ']'
FROM (
SELECT DISTINCT PIVOT_CODE
FROM (
SELECT TEST AS PIVOT_CODE
FROM #tmp
) AS rows
) AS PIVOT_CODES

SET @sql = '
SELECT Name, ' + @select_list + '
FROM
(
SELECT b.Name, RowNum, b.EffectiveDate, b.TEST AS PIVOT_CODE
FROM
(
SELECT Name, Test, EffectiveDate, ROW_NUMBER() OVER (PARTITION BY NAME, TEST ORDER BY EffectiveDate) RowNum
FROM #Tmp
) b
) p
PIVOT (
MIN(EffectiveDate)
FOR PIVOT_CODE IN (
' + @pivot_list + '
)
) AS pvt
ORDER BY Name, RowNum
'


PRINT @sql

EXEC (@sql)


DROP TABLE #TMP

How to declare variables within dynamic columns in SQL Server 2005 using PIVOT

No, it's NOT there.
Try this:

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

DECLARE @wsDateFrom AS smalldatetime
DECLARE @wsDateTo AS smalldatetime

SET @wsDateFrom = '01-JAN-2015'
SET @wsDateTo = '30-JUN-2015'

select @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(a.AbsenceDescription)
FROM dbo.tblAbsentCodes AS a
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')

set @query = N'SELECT StudentID, ' + @cols +
'FROM (SELECT a.StudentID, ab.AbsenceDescription, a.AttendanceID
FROM dbo.tblAttendance AS a
INNER JOIN tblCalendar c
ON a.DateID = c.DateID
INNER JOIN tblAbsentCodes as ab
ON ab.AbsenceID = a.AbsenceID
WHERE c.DayDate BETWEEN ' + @wsDateFrom + 'AND ' + @wsDateTo + ' ) AS p
PIVOT (COUNT(AttendanceID) FOR AbsenceDescription IN (' + @cols + ')) AS pvt '

execute(@query)


Related Topics



Leave a reply



Submit