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
How to Create Multiple One to One's
Delete Duplicate Records in SQL Server
Get Day of Week in SQL Server 2005/2008
Query With Left Join Not Returning Rows For Count of 0
Using Stored Procedure in Classical Asp .. Execute and Get Results
Group by and Aggregate Sequential Numeric Values
How to Use (Install) Dblink in Postgresql
Bulk Delete on SQL Server 2008 (Is There Anything Like Bulk Copy (Bcp) for Delete Data)
MySQL Select Dynamic Row Values as Column Names, Another Column as Value
Check If MySQL Table Exists Without Using "Select From" Syntax
How to Generate a Range of Dates in SQL Server
Mixing Ansi 1992 Joins and Commas in a Query
Default Row Order in Select Query - SQL Server 2008 VS SQL 2012
Stored Procedure or Function Expects Parameter Which Is Not Supplied