Solved

Flatten a table

Posted on 2006-07-17
3
563 Views
Last Modified: 2008-06-18
I'm using SQL Server 2K and I need to flatten a table for a report.

The table looks like this:

ID            PARENTID           StartDate
_________________________________
1               1                       1/1/2006
2               1                       2/1/2006
3               1                       3/1/2006
4               2                       1/1/2005
5               2                       2/1/2005
6               3                       1/1/2004
7               3                       2/1/2004
8               3                       3/1/2004
9               3                       4/1/2004


I need a query or procedure that produces this:

PARENTID           StartDate1      StartDate2    StartDate3     StartDate4
__________________________________________________________
1                       1/1/2006        2/1/2006        3/1/2006       NULL
2                       1/1/2005        2/1/2005        NULL             NULL
3                       1/1/2004        2/1/2004        3/1/2004       4/1/2004

Thank you in advance.
0
Comment
Question by:RStuppi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 17127980
do you know how many start dates there are?
which version of sql server?
if  2005 then consider the pivot phrase...



if not use this...

http://www.microsoft.com/sql/ce/techinfo/20bol.asp

EXEC sp_TRANSFORM 'SUM','PM_EmpAssignedID','vw_Loc_Pos_Dflt_Rem2','PM_PositionsID','Position'

IF OBJECT_ID('sp_Transform', 'P') IS NOT NULL
     DROP PROCEDURE sp_Transform
GO
CREATE PROCEDURE sp_TRANSFORM
/*
   Purpose:     Creates a Pivot(tm) table for the specified table,
                view or select statement
   Author:      svenh@itrain.de
   Version:     1.1
   History:     march 2000 version 1.0
                july 2002  version 1.1

   Input parameters:
       @Aggregate_Function (optional)
            the aggregate function to use for the pivot
                default function is SUM
        @Aggregate_Column
                name of column for aggregate
        @TableOrView_Name
                name of table or view to use
                if name contains spaces or other special
                characters [] should be used
                Can also be a valid SELECT statement
        @Select_Column
                Column for first column in result table
                for this column row values are displayed
        @Pivot_Column
                Column that is transformed into columns
                for this column column values are displayed
     @DEBUG
          Set this flag to 1 to get debug-information
   
      Example usage:
        Table given   aTable
        content:      Product    Salesman    Sales
                      P1         Sa          12
                      P2         Sb          10
                      P2         Sb          3
                      P3         Sa          12
                      P1         Sc          8
                      P3         Sa          1
                      P2         Sa          NULL
     CALL
     EXEC sp_Transform 'SUM', 'Sales', 'aTable', 'Product', 'Salesman'
or      EXEC sp_Transform @Aggregate_Column='Sales', @TableOrViewName='aTable',
                            @Select_Column='Product', @Pivot_Column='Salesman'

Result:
        Product| Sa       | Sb       | Sc      | Total      
        -------+----------+----------+---------+---------
        P1     | 12,00    |  0,00    |  8,00   |  20,00
        P2     |  0,00    | 13,00    |  0,00   |  13,00
        P3     | 13,00    |  0,00    |  0,00   |  13,00
        -------+----------+----------+---------+---------
        Total  | 25,00    | 13,00    |  8,00   |  46,00


*/
     @Aggregate_Function nvarchar(30) = 'SUM',
     @Aggregate_Column   nvarchar(255),
     @TableOrView_Name   nvarchar(255),
     @Select_Column         nvarchar(255),
     @Pivot_Column       nvarchar(255),
     @DEBUG              bit = 0
AS
SET NOCOUNT ON
DECLARE @TransformPart   nvarchar(4000)
DECLARE @SQLColRetrieval nvarchar(4000)
DECLARE @SQLSelectIntro  nvarchar(4000)
DECLARE @SQLSelectFinal  nvarchar(4000)

IF @Aggregate_Function NOT IN ('SUM', 'COUNT', 'MAX', 'MIN', 'AVG', 'STDEV', 'VAR', 'VARP', 'STDEVP')  
     BEGIN RAISERROR ('Invalid aggregate function: %s', 10, 1, @Aggregate_Function) END
ELSE
BEGIN
     SELECT @SQLSelectIntro = 'SELECT CASE WHEN (GROUPING('  +
                         QUOTENAME(@Select_Column)       +
                    ') = 1) THEN ''Total'' ELSE '   +
                    'CAST( + '                      +
                                QUOTENAME(@Select_Column)       +
                    ' AS NVARCHAR(255)) END As '    +
                    QUOTENAME(@Select_Column)       +
                    ', '
     IF @DEBUG = 1 PRINT @sqlselectintro
     SET @SQLColRetrieval =
     N'SELECT @TransformPart = CASE WHEN @TransformPart IS NULL THEN ' +
               N'''' + @Aggregate_Function + N'(CASE CAST(' +
               QUOTENAME(CAST(@Pivot_Column AS VARCHAR(255))) +
               N' AS VARCHAR(255)) WHEN '''''' + CAST('  +
               QUOTENAME(@Pivot_Column) +
               N' AS NVarchar(255)) + '''''' THEN ' + @Aggregate_Column +
               N' ELSE 0 END) AS '' + QUOTENAME(' +
               QUOTENAME(CAST(@Pivot_Column AS VARCHAR(255))) +
               N') ELSE  @TransformPart + '', ' + @Aggregate_Function +
               N' (CASE CAST(' + QUOTENAME(@Pivot_Column) +
               N' AS nVARCHAR(255)) WHEN '''''' + CAST(' +
               QUOTENAME(CAST(@Pivot_Column As VarChar(255))) +
               N' AS nVARCHAR(255)) + '''''' THEN ' +
               @Aggregate_Column +
               N' ELSE 0 END) AS '' + QUOTENAME(' +
               QUOTENAME(CAST(@Pivot_Column AS VARCHAR(255))) +
               N') END FROM (SELECT DISTINCT ' +
               QUOTENAME(CAST(@Pivot_Column AS VARCHAR(255))) +
               N' FROM ' + @TableOrView_Name + ') SelInner'
     IF @DEBUG = 1 PRINT @SQLColRetrieval    
     EXEC sp_executesql @SQLColRetrieval,
                           N'@TransformPart nvarchar(4000) OUTPUT',
                           @TransformPart OUTPUT
     IF @DEBUG = 1 PRINT @TransformPart
     SET @SQLSelectFinal =
                           N', ' + @Aggregate_Function + N'(' +
                           CAST(@Aggregate_Column As Varchar(255)) +
                           N') As Total FROM ' + @TableOrView_Name + N' GROUP BY ' +
                           @Select_Column + N' WITH CUBE'
     IF @DEBUG = 1 PRINT @SQLSelectFinal
     EXEC (@SQLSelectIntro + @TransformPart + @SQLSelectFinal)
END
GO


/*
To test the preceding stored procedure uncomment the
following section

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
     WHERE TABLE_NAME = 'aTable')
   DROP TABLE aTable  
--GO
 
CREATE TABLE aTable(Product varchar(2), Salesman varchar(2), Sales int)
INSERT INTO aTable VALUES('P1', 'Sa', 12)
INSERT INTO aTable VALUES('P2', 'Sb', 10)
INSERT INTO aTable VALUES('P2', 'Sb', 3)
INSERT INTO aTable VALUES('P3', 'Sa', 12)
INSERT INTO aTable VALUES('P1', 'Sc', 8)
INSERT INTO aTable VALUES('P3', 'Sa', 1)
INSERT INTO aTable VALUES('P2', 'Sa', NULL)

EXEC sp_Transform @Aggregate_Column='Sales', @TableOrView_Name='aTable',
                            @Select_Column='Product', @Pivot_Column='Salesman'

*/
-- Example from Northwind Table
-- USE NORTHWIND
-- GO
-- EXEC sp_transform 'sum', 'unitprice', 'products', 'productname', 'CategoryID'
-- GO


 
0

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server for XML PATH giving wrong results. 6 59
SSRS  - Parameters with comma 10 40
VM SQL server license. 1 61
SQL Server Error: 4060 8 30
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question