Converting rows into columns

  • 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

     

  • 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

  • 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

  • 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

  • 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

  • Here is another similar approach. By the way, you are not alone on this journey to CROSSTAB queries in MS T-SQL.

    http://www.sqlteam.com/item.asp?ItemID=2955

  • 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