April 23, 2007 at 7:18 am
Hi All
I have the following resultset:
PCName ObjName PerfName Date values 00:00 00:05 00:10 00:15 00:20
asd mjdd kkkk 12/04/2007 20.10 984 849 933 976 7667
jsss jkjjjk ksklsk 13/04/2007 920 933 933 030 833 933
The last 5 columns are timestamped columns.
I want the resultset to be like the folllowing:
PCName ObjName PerfName Date Values Col1
asd mjdd kkkk 12/04/2007 20.10 00:00
920 00:05
00:10
00:15
00:20
Because I have to do a summary on the values of the table.
Can anyone provide me with a solution for this, please?
Anchelin
April 23, 2007 at 8:11 am
Anchelin
Two questions:
(1) Have you read about the PIVOT function?
(2) You say that you have the "resultset" that you gave, but is that the result of a query other than SELECT * FROM ..... ? If so, please will you provide DDL for the table(s) from which you wish to derive your summary.
Thanks
John
April 24, 2007 at 3:58 am
Hi John
Not really, but I'm on google now. Is there perhaps another site that can give me a broader explanation on PIVOT function? Please?
Here's my script for my table:
CREATE TABLE [dbo].[FullDaily_Ebi] (
[Time (HHMM) ->] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[h] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[f] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[c] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[0000] [float] NULL ,
[0005] [float] NULL ,
[0010] [float] NULL ,
[0015] [float] NULL ,
[0020] [float] NULL ,
[0025] [float] NULL ,
[0030] [float] NULL ,
[0035] [float] NULL ,
[0040] [float] NULL ,
[0045] [float] NULL ,
[0050] [float] NULL ,
[0055] [float] NULL ,
[0100] [float] NULL ,
[0105] [float] NULL ,
[0110] [float] NULL ,
[0115] [float] NULL ,
[0120] [float] NULL ,
[0125] [float] NULL ,
[0130] [float] NULL ,
[0135] [float] NULL ,
[0140] [float] NULL ,
[0145] [float] NULL ,
[0150] [float] NULL ,
[0155] [float] NULL ,
[0200] [float] NULL ,
[0205] [float] NULL ,
[0210] [float] NULL ,
[0215] [float] NULL ,
[0220] [float] NULL ,
[0225] [float] NULL ,
[0230] [float] NULL ,
[0235] [float] NULL ,
[0240] [float] NULL ,
[0245] [float] NULL ,
[0250] [float] NULL ,
[0255] [float] NULL ,
[0300] [float] NULL ,
[0305] [float] NULL ,
[0310] [float] NULL ,
[0315] [float] NULL ,
[0320] [float] NULL ,
[0325] [float] NULL ,
[0330] [float] NULL ,
[0335] [float] NULL ,
[0340] [float] NULL ,
[0345] [float] NULL ,
[0350] [float] NULL ,
[0355] [float] NULL ,
[0400] [float] NULL ,
[0405] [float] NULL ,
[0410] [float] NULL ,
[0415] [float] NULL ,
[0420] [float] NULL ,
[0425] [float] NULL ,
[0430] [float] NULL ,
[0435] [float] NULL ,
[0440] [float] NULL ,
[0445] [float] NULL ,
[0450] [float] NULL ,
[0455] [float] NULL ,
[0500] [float] NULL ,
[0505] [float] NULL ,
[0510] [float] NULL ,
[0515] [float] NULL ,
[0520] [float] NULL ,
[0525] [float] NULL ,
[0530] [float] NULL ,
[0535] [float] NULL ,
[0540] [float] NULL ,
[0545] [float] NULL ,
[0550] [float] NULL ,
[0555] [float] NULL ,
[0600] [float] NULL ,
[0605] [float] NULL ,
[0610] [float] NULL ,
[0615] [float] NULL ,
[0620] [float] NULL ,
[0625] [float] NULL ,
[0630] [float] NULL ,
[0635] [float] NULL ,
[0640] [float] NULL ,
[0645] [float] NULL ,
[0650] [float] NULL ,
[0655] [float] NULL ,
[0700] [float] NULL ,
[0705] [float] NULL ,
[0710] [float] NULL ,
[0715] [float] NULL ,
[0720] [float] NULL ,
[0725] [float] NULL ,
[0730] [float] NULL ,
[0735] [float] NULL ,
[0740] [float] NULL ,
[0745] [float] NULL ,
[0750] [float] NULL ,
[0755] [float] NULL ,
[0800] [float] NULL ,
[0805] [float] NULL ,
[0810] [float] NULL ,
[0815] [float] NULL ,
[0820] [float] NULL ,
[0825] [float] NULL ,
[0830] [float] NULL ,
[0835] [float] NULL ,
[0840] [float] NULL ,
[0845] [float] NULL ,
[0850] [float] NULL ,
[0855] [float] NULL ,
[0900] [float] NULL ,
[0905] [float] NULL ,
[0910] [float] NULL ,
[0915] [float] NULL ,
[0920] [float] NULL ,
[0925] [float] NULL ,
[0930] [float] NULL ,
[0935] [float] NULL ,
[0940] [float] NULL ,
[0945] [float] NULL ,
[0950] [float] NULL ,
[0955] [float] NULL ,
[1000] [float] NULL ,
[1005] [float] NULL ,
[1010] [float] NULL ,
[1015] [float] NULL ,
[1020] [float] NULL ,
[1025] [float] NULL ,
[1030] [float] NULL ,
[1035] [float] NULL ,
[1040] [float] NULL ,
[1045] [float] NULL ,
[1050] [float] NULL ,
[1055] [float] NULL ,
[1100] [float] NULL ,
[1105] [float] NULL ,
[1110] [float] NULL ,
[1115] [float] NULL ,
[1120] [float] NULL ,
[1125] [float] NULL ,
[1130] [float] NULL ,
[1135] [float] NULL ,
[1140] [float] NULL ,
[1145] [float] NULL ,
[1150] [float] NULL ,
[1155] [float] NULL ,
[1200] [float] NULL ,
[1205] [float] NULL ,
[1210] [float] NULL ,
[1215] [float] NULL ,
[1220] [float] NULL ,
[1225] [float] NULL ,
[1230] [float] NULL ,
[1235] [float] NULL ,
[1240] [float] NULL ,
[1245] [float] NULL ,
[1250] [float] NULL ,
[1255] [float] NULL ,
[1300] [float] NULL ,
[1305] [float] NULL ,
[1310] [float] NULL ,
[1315] [float] NULL ,
[1320] [float] NULL ,
[1325] [float] NULL ,
[1330] [float] NULL ,
[1335] [float] NULL ,
[1340] [float] NULL ,
[1345] [float] NULL ,
[1350] [float] NULL ,
[1355] [float] NULL ,
[1400] [float] NULL ,
[1405] [float] NULL ,
[1410] [float] NULL ,
[1415] [float] NULL ,
[1420] [float] NULL ,
[1425] [float] NULL ,
[1430] [float] NULL ,
[1435] [float] NULL ,
[1440] [float] NULL ,
[1445] [float] NULL ,
[1450] [float] NULL ,
[1455] [float] NULL ,
[1500] [float] NULL ,
[1505] [float] NULL ,
[1510] [float] NULL ,
[1515] [float] NULL ,
[1520] [float] NULL ,
[1525] [float] NULL ,
[1530] [float] NULL ,
[1535] [float] NULL ,
[1540] [float] NULL ,
[1545] [float] NULL ,
[1550] [float] NULL ,
[1555] [float] NULL ,
[1600] [float] NULL ,
[1605] [float] NULL ,
[1610] [float] NULL ,
[1615] [float] NULL ,
[1620] [float] NULL ,
[1625] [float] NULL ,
[1630] [float] NULL ,
[1635] [float] NULL ,
[1640] [float] NULL ,
[1645] [float] NULL ,
[1650] [float] NULL ,
[1655] [float] NULL ,
[1700] [float] NULL ,
[1705] [float] NULL ,
[1710] [float] NULL ,
[1715] [float] NULL ,
[1720] [float] NULL ,
[1725] [float] NULL ,
[1730] [float] NULL ,
[1735] [float] NULL ,
[1740] [float] NULL ,
[1745] [float] NULL ,
[1750] [float] NULL ,
[1755] [float] NULL ,
[1800] [float] NULL ,
[1805] [float] NULL ,
[1810] [float] NULL ,
[1815] [float] NULL ,
[1820] [float] NULL ,
[1825] [float] NULL ,
[1830] [float] NULL ,
[1835] [float] NULL ,
[1840] [float] NULL ,
[1845] [float] NULL ,
[1850] [float] NULL ,
[1855] [float] NULL ,
[1900] [float] NULL ,
[1905] [float] NULL ,
[1910] [float] NULL ,
[1915] [float] NULL ,
[1920] [float] NULL ,
[1925] [float] NULL ,
[1930] [float] NULL ,
[1935] [float] NULL ,
[1940] [float] NULL ,
[1945] [float] NULL ,
[1950] [float] NULL ,
[1955] [float] NULL ,
[2000] [float] NULL ,
[2005] [float] NULL ,
[2010] [float] NULL ,
[2015] [float] NULL ,
[2020] [float] NULL ,
[2025] [float] NULL ,
[2030] [float] NULL ,
[2035] [float] NULL ,
[2040] [float] NULL ,
[2045] [float] NULL
) ON [PRIMARY]
GO
Thanks in advance
Anchelin
April 24, 2007 at 4:46 am
Anchelin
I would recommend reviewing your database design and normalising it if possible. If that isn't an option, the PIVOT function is described in Books Online:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/36b19e68-94f6-4539-aeb1-79f5312e4263.htm
I've never used it myself, but I'm sure if you're having problems with it and you post back then somebody should be able to help you.
John
April 24, 2007 at 5:58 am
Here is a sample SP someone wrote on this forum a couple of weeks ago, probably more. I think it works out of the Northwind Sample Db.
I hope you can figure out how to adapt it to your needs. If not, someone else worte a GENERIC one, kind of long SP, and a little more complicated, but it worked for all my tests. Good luck.
CREATE PROCEDURE uspSalesOfficeDepartmentPivot
(
declare
@FromDate DATETIME,
@ToDate DATETIME
)
AS
SET NOCOUNT ON
set @FromDate = '2001-07-01'
set @ToDate = '2004-07-31'
CREATE TABLE #Aggregates
(
RowText VARCHAR(50),
RowOrder int,
ColumnText VARCHAR(50),
CellData MONEY
)
INSERT INTO #Aggregates
(
RowText
,RowOrder
,ColumnText
,CellData
)
SELECT
datename(mm,[OrderDate])
,month([OrderDate])
,B.[Name]
,sum([SubTotal])
FROM [AdventureWorks].[Sales].[SalesOrderHeader] A
INNER JOIN [AdventureWorks].[Sales].[SalesTerritory] B ON
A.[TerritoryID] = B.[TerritoryID]
group by datename(mm,[OrderDate])
,month([OrderDate])
,B.[Name]
HAVING sum([SubTotal]) <> 0
CREATE UNIQUE INDEX IX_Aggregates ON #Aggregates (RowText, ColumnText, CellData)
CREATE TABLE #Columns
(
ColumnIndex INT IDENTITY (0, 1),
ColumnText VARCHAR(50)
)
INSERT INTO #Columns
(
ColumnText
)
SELECT DISTINCT ColumnText
FROM #Aggregates --(INDEX(IX_Aggregates), NOLOCK)
ORDER BY ColumnText
CREATE UNIQUE INDEX IX_Columns ON #Columns (ColumnIndex, ColumnText)
CREATE TABLE #Rows(
RowText VARCHAR(50)
,RowOrder int)
INSERT INTO #Rows(RowText,RowOrder)
SELECT DISTINCT
RowText
,RowOrder
FROM #Aggregates
CREATE UNIQUE INDEX IX_Rows ON #Rows (RowText)
DECLARE @ColumnIndex INT,
@MaxColumnIndex INT,
@ColumnText VARCHAR(50),
@sql VARCHAR(1000)
SELECT @ColumnIndex = 0,
@MaxColumnIndex = MAX(ColumnIndex)
FROM #Columns
WHILE @ColumnIndex <= @MaxColumnIndex
BEGIN
SELECT @ColumnText = ColumnText
FROM #Columns
WHERE ColumnIndex = @ColumnIndex
SELECT @sql = 'ALTER TABLE #Rows ADD ' + QUOTENAME(@ColumnText) + ' INT NULL DEFAULT 0'
EXEC (@SQL)
SELECT @sql = 'UPDATE #Rows SET ' + QUOTENAME(@ColumnText) + ' = #Aggregates.CellData
FROM #Aggregates
, #Columns
WHERE #Rows.RowText = #Aggregates.RowText
AND #Columns.ColumnText = #Aggregates.ColumnText
AND #Columns.ColumnIndex = ' + CAST(@ColumnIndex AS VARCHAR(12))
EXEC (@SQL)
SELECT @ColumnIndex = @ColumnIndex + 1
END
DROP TABLE #Columns
DROP TABLE #Aggregates
SELECT #Rows.*
FROM #Rows
ORDER BY #Rows.RowOrder
DROP TABLE #Rows
April 24, 2007 at 6:04 am
Here is another similar approach. By the way, you are not alone on this journey to CROSSTAB queries in MS T-SQL.
April 24, 2007 at 6:10 am
Thanks Camilo
I will work through it and let you know what my outcome is.
Thanks alot!!
Anchelin
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply