Pivot using SQL Server 2000
We've successfully used the following approach in the past...
SELECT [p].ProductID,
[p].Name,
MAX(CASE [m].MetaKey
WHEN 'A'
THEN [m].MetaValue
END) AS A,
MAX(CASE [m].MetaKey
WHEN 'B'
THEN [m].MetaValue
END) AS B,
MAX(CASE [m].MetaKey
WHEN 'C'
THEN [m].MetaValue
END) AS C
FROM Products [p]
INNER JOIN ProductMeta [m]
ON [p].ProductId = [m].ProductId
GROUP BY [p].ProductID,
[p].Name
It can also be useful transposing aggregations with the use of...
SUM(CASE x WHEN 'y' THEN yVal ELSE 0 END) AS SUMYVal
EDIT
Also worth noting this is using ANSI standard SQL and so it will work across platforms :)
Sql server 2000 pivot query
Select Vessel
, Min ( Case When Month([Date]) = 1 Then Inspector End ) As Jan
, Min ( Case When Month([Date]) = 2 Then Inspector End ) As Feb
, Min ( Case When Month([Date]) = 3 Then Inspector End ) As Mar
...
, Min ( Case When Month([Date]) = 12 Then Inspector End ) As Dec
From Table
Group By Vessel
You have to statically declare the columns. It should be noted that this will only work for a single year. If what you are seeking is dynamic column (month) generation, then trying to do it in T-SQL is not the right approach as you can only do it with some fugly dynamic SQL. Instead, you should use a report generator or middle-tier component to build the result set.
How can I do a dynamic pivot in SQL Server 2000
We create SQL commands with the CASE statements from our application and fire them at the database (any database, not specifically SQL server). First we determine the number of pivot columns and their names using one query, from those results we generate the next query.
So the first query to determine the columns looks somewhat like:
SELECT DISTINCT myField FROM myTable
Then we use all the values in this result to construct an SQL command where a CASE statement is generated for each value.
We wanted a databasebase agnostic solution so we do this processing outside the database but i'm sure you could do the same in a stored procedure withing SQL server itself.
SQL 2000 Pivot for job timeline.
I managed to answer this myself so thought i would post in case anyone else needs
--
****************************************************************************************
-- This script returns a (graphical) timeline for all SQL jobs for use on SQL Server 2000
--****************************************************************************************
-- Version: 2.0
-- Author: Ryan Wooster
-- Date: 18-6-2013
--****************************************************************************************
set nocount on
declare @Minutes table (DT datetime)
--declare @JobNames table (JobName varchar(255))
declare @DT datetime
declare @StartDT varchar(20)
declare @EndDT varchar(20)
declare @Resolution int
declare @RemoveNonactiveJobs int
declare @IgnoreDisabledJobs int
declare @DSQLT varchar(4000)
declare @DSQL varchar(4000)
declare @Server varchar(20)
--***************************************************************************************
-- Set variables
--***************************************************************************************
set @StartDT = getdate() - 1
set @EndDT = getdate()
set @Resolution = 1 -- Enter the Resolution in minutes
set @RemoveNonactiveJobs = 1
set @IgnoreDisabledJobs = 1
set @Server = 'IGSQ03UK'
--***************************************************************************************
-- Pre-run cleanup (just in case)
--***************************************************************************************
IF OBJECT_ID('tempdb..#Timeline') IS NOT NULL DROP TABLE #Timeline;
IF OBJECT_ID('tempdb..#JobRuntime') IS NOT NULL DROP TABLE #JobRuntime;
IF OBJECT_ID('tempdb..#Pivot') IS NOT NULL DROP TABLE #Pivot;
IF OBJECT_ID('tempdb..#tblTLine') IS NOT NULL DROP TABLE #tblTLine;
IF OBJECT_ID('tempdb..#tblTasks') IS NOT NULL DROP TABLE #tblTasks;
IF OBJECT_ID('tempdb..#JobNames') IS NOT NULL DROP TABLE #JobNames;
IF OBJECT_ID('tempdb..#JobRuntime') IS NOT NULL DROP TABLE #JobRuntime;
--***************************************************************************************
--Create Temp Tables for use in Dynamic SQL
--***************************************************************************************
CREATE TABLE #JobNames (JobName varchar(128))
CREATE TABLE #Timeline (DT datetime, JobName varchar(128), Active int)
CREATE TABLE #JobRuntime (name varchar(128), SDT datetime, EDT datetime, DurationSeconds datetime)
--***************************************************************************************
-- Make a Jobname table
--***************************************************************************************
--'+ @IgnoreDisabledJobs +'
SET @DSQLT='
insert into #JobNames (JobName)
select replace(name, '' '', ''.'') --Replace spaces (they are invalid in XML based pivot lower in the code)
from '+ @Server +'.msdb.dbo.sysjobs
where enabled = 1
--WHERE NAME = ''<dbname>''
--WHERE NAME like ''%<partial dbname>%'''
--Print @DSQLT
EXEC (@DSQLT)
--***************************************************************************************
-- Genereate a Datetime table between StartDT and EndDT with x minute Resolution
--***************************************************************************************
set @DT = @StartDT
WHILE @DT < @EndDT
begin
insert into @Minutes (DT) values(@DT)
set @DT= dateadd(mi, @Resolution, @DT)
end
--***************************************************************************************
-- Create a timeline table by crossjoining the Datetime and Jobnames tables
--***************************************************************************************
insert into #Timeline
select DT, JobName, 0 as Active
from @Minutes cross join #JobNames
--***************************************************************************************
-- Create the Job Runtime information table
--***************************************************************************************
SET @DSQL=
'INSERT into #JobRuntime
select replace(name, '' '', ''.'') as name --Replace spaces (they are invalid in XML based pivot lower in the code)
--,step_id
--,step_name
,CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + '' '' + STUFF(STUFF(RIGHT(''000000'' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, '':''), 3, 0, '':''), 120) as SDT
,dateadd( s,
((run_duration/10000)%100 * 3600) + ((run_duration/100)%100 * 60) + run_duration%100 ,
CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + '' '' + STUFF(STUFF(RIGHT(''000000'' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, '':''), 3, 0, '':''), 120)
) as EDT
--,CAST(STUFF(STUFF(REPLACE(STR(run_duration, 6), '' '', ''0''), 3, 0, '':''), 6, 0, '':'') AS time(0)) AS Duration
,((run_duration/10000)%100 * 3600) + ((run_duration/100)%100 * 60) + run_duration%100 DurationSeconds
FROM '+ @Server +'.msdb.dbo.sysjobs job
left JOIN '+ @Server +'.msdb.dbo.sysjobhistory his
ON his.job_id = job.job_id
where CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + '' '' + STUFF(STUFF(RIGHT(''000000'' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, '':''), 3, 0, '':''), 120) between '''+ @StartDT +''' and '''+ @EndDT +'''
and job.name not in (''Database Mirroring Monitor Job'', ''<dbname>'')
and step_id = 0 -- step_id = 0 is the entrite job, step_id > 0 is actual step number
--and ((run_duration/10000)%100 * 3600) + ((run_duration/100)%100 * 60) + run_duration%100 > 1 -- Ignore trivial runtimes
order by SDT'
EXEC (@DSQL)
--***************************************************************************************
-- Update the Timeline based on the the Job Runtime information table
--***************************************************************************************
update #Timeline
set Active = 1
from #Timeline inner join #JobRuntime
on JobName = name
and (
SDT between dt and dateadd(mi, @Resolution - 1, DT) -- Start point (added for Resolution support)
or
EDT between dt and dateadd(mi, @Resolution, DT) -- End point (added for Resolution support)
or
DT between SDT and EDT
)
--***************************************************************************************
-- Delete all jobs from the Timeline that that had no activity
--***************************************************************************************
if @RemoveNonactiveJobs = 1
delete
from #Timeline
where JobName in ( select Jobname
from #Timeline
group by Jobname
having sum(active) = 0 )
--***************************************************************************************
-- Build and Pivot the Timeline table
--***************************************************************************************
create table #Pivot (DT varchar(250) null, Name varchar(250) null, Active int null)
-- col1 = row, col2 = column, col3 = data
insert into #Pivot
select convert(varchar(250), DT, 120), JobName, Active
from #Timeline
--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DECLARE @SQL nvarchar(4000)
DECLARE @TaskName nvarchar(100)
SET NOCOUNT ON
CREATE TABLE #tblTLine (
[DT] varchar(200)
)
CREATE TABLE #tblTasks (
[Tasks] varchar(200)
)
INSERT INTO #tblTasks (
[Tasks]
)
select DISTINCT
Name
from #Pivot
INSERT INTO #tblTLine (
[DT]
)
select DISTINCT
[DT]
from #Pivot
ORDER BY DT
--WHERE Active = 1
-- Build Table
DECLARE cur CURSOR FOR
select DISTINCT
[Tasks]
from #tblTasks
OPEN cur
FETCH NEXT FROM cur INTO @TaskName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'ALTER TABLE #tblTLine ADD [' + @TaskName + '] nchar(1) NULL'
EXEC (@SQL)
SET @SQL = ''
SET @SQL = 'UPDATE #tblTLine SET [' + @TaskName + '] = ''0'''
EXEC (@SQL)
FETCH NEXT FROM cur INTO @TaskName
END
CLOSE cur
DEALLOCATE cur
-- Update Table
DECLARE @SQLUpdate nvarchar(4000)
DECLARE @Time nvarchar(100)
DECLARE @Name nvarchar(100)
DECLARE @Active nchar(1)
DECLARE curUpdate CURSOR FOR
SELECT
[DT],
[Name],
[Active]
FROM #Pivot
WHERE Active = 1
OPEN curUpdate
FETCH NEXT FROM curUpdate INTO @Time, @Name, @Active
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLUpdate = 'UPDATE #tblTLine SET [' + @Name + '] = ''1'' WHERE [DT] = ''' + @Time + ''''
EXEC (@SQLUpdate)
FETCH NEXT FROM curUpdate INTO @Time, @Name, @Active
END
CLOSE curUpdate
DEALLOCATE curUpdate
SET NOCOUNT OFF
--***************************************************************************************
-- Output the Timeline table
--***************************************************************************************
SELECT * FROM #tblTLine
--***************************************************************************************
-- Cleanup
--***************************************************************************************
IF OBJECT_ID('tempdb..#Timeline') IS NOT NULL DROP TABLE #Timeline;
IF OBJECT_ID('tempdb..#JobRuntime') IS NOT NULL DROP TABLE #JobRuntime;
IF OBJECT_ID('tempdb..#Pivot') IS NOT NULL DROP TABLE #Pivot;
IF OBJECT_ID('tempdb..#tblTLine') IS NOT NULL DROP TABLE #tblTLine;
IF OBJECT_ID('tempdb..#tblTasks') IS NOT NULL DROP TABLE #tblTasks;
IF OBJECT_ID('tempdb..#JobNames') IS NOT NULL DROP TABLE #JobNames;
IF OBJECT_ID('tempdb..#JobRuntime') IS NOT NULL DROP TABLE #JobRuntime;
--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL Server 2000: Pivot with conditions
In SQL Server 2000 there are not so many options available. You can use UNION ALL for this:
SELECT 'a' AS type, a
FROM mytable
WHERE a IS NOT NULL AND a <> 'NA'
UNION ALL
SELECT 'b' AS type, b
FROM mytable
WHERE b IS NOT NULL AND b <> 'NA'
UNION ALL
SELECT 'c' AS type, c
FROM mytable
WHERE c IS NOT NULL AND c <> 'NA'
UNION ALL
SELECT 'd' AS type, d
FROM mytable
WHERE d IS NOT NULL AND d <> 'NA'
Rows to Columns in SQL Server 2000
Since SQL Server 2000 does not have the PIVOT function, you should be able to use something similar to the following:
DECLARE @query AS NVARCHAR(4000)
DECLARE @rowCount as int
DECLARE @pivotCount as int
DECLARE @pivotRow as varchar(10)
set @rowCount = 1
set @pivotRow = ''
create table #colsPivot
(
id int IDENTITY(1,1),
name varchar(20),
CustId int
)
insert into #colsPivot
select 'Observacion', IDObservacionCustomer
from yourtable
set @pivotCount= (select COUNT(*) from #colsPivot)
-- reset rowcount
set @rowCount = 1
set @query = ''
---- create the CASE string
while @rowCount <= @pivotCount
begin
set @pivotRow = (select Top 1 CustId from #colsPivot)
set @query = @query + ', max(case when IDObservacionCustomer = ''' + @pivotRow + ''' then Observacion end) as ''Observacion_' + cast(@rowCount as varchar(10)) + ''''
delete from #colsPivot where CustId = @pivotRow
if @rowCount <= @pivotCount
begin
set @rowCount = @rowCount + 1
end
end
-- add the rest of the SQL Statement
set @query = 'SELECT IDProspecto ' + @query + ' from yourtable group by IDProspecto'
exec(@query)
See SQL Fiddle With Demo
SQL Server 2000 pivot without temporary tables?
I think you may be a bit confused as to what temporary tables are.
You should be able to create a temp table (in memory) within your SQL statement. No extra permissions are necessary.
Related Topics
Why (And How) to Split Column Using Master..Spt_Values
Return a Value If No Record Is Found
Is There Ever a Time Where Using a Database 1:1 Relationship Makes Sense
Execute Stored Procedure from a Function
How to Delete from Select in MySQL
How to Implement Referential Integrity in Subtypes
SQL Where Id in (Id1, Id2, ..., Idn)
Insert Rows into Multiple Tables in a Single Query, Selecting from an Involved Table
Performance Issue in Using Select *
Optimized SQL for Tree Structures
Preventing Adjacent/Overlapping Entries with Exclude in Postgresql
Get the Last Day of the Month in SQL
How to Update Top 100 Records in SQL Server
SQL Query - Concatenating Results into One String
Does Ms SQL Server's "Between" Include the Range Boundaries
How to Delete Duplicate Rows with SQL
How to Have Nhibernate Only Generate the SQL Without Executing It