none
dynamic pivoting

    Question

  • hi, 

    i didn't use pivoting before, i need dynamic pivoting, i wrote a procedure for it, and it is generating error

    i have 3 tables

    customer(customer_ID, customer_fname,.......)

    extension_attributes(extension_ID, extension_label, type_ID)

    extension_value(extension_ID, customer_ID, attribute_value)

    procedure:

     

    declare @cols varchar(1000)
    select @cols=stuff((select distinct N'['+extension_label+'],' from extention_attributes where provider_ID=1 for xml path('')),1,0,'')
    select @cols=left(@cols, len(@cols)-1)
    print @cols
    
    select customer_fname, @cols
    from
    (
    select customer_fname, a.extension_ID, extension_label, attribute_value 
    from customer 
    join extension_values as v on customer.customer_ID=v.customer_ID
    join extension_attributes as a on a.extension_ID=v.extension_ID 
    )as srcTable 
    pivot 
    (sum(attribute_value) for extension_label in (@cols))as pivotTable
    

    i want the result showing me the extension labels as column headers and attribute values as value in that column 

    help me correct it please

    thanks alot.

     

    Sunday, April 18, 2010 1:29 PM

Answers

  • CREATE TABLE Sales
    (
    SaleID int IDENTITY PRIMARY KEY CLUSTERED,
    ClientID int,
    Date datetime,
    Amount money
    )
    insert Sales values(1,'20010401', 15.48)
    insert Sales values(1,'20020302', 134.01)
    insert Sales values(1,'20031003', 2346.03)
    insert Sales values(2,'20030203', 754.88)
    insert Sales values(3,'20010301', 73.07)
    insert Sales values(3,'20030402', 734.46)
    insert Sales values(4,'20010301', 1567.10)
    insert Sales values(4,'20020404', 6575.70)
    insert Sales values(4,'20030307', 6575.77)
    insert Sales values(4,'20030309', 6575.37)
    insert Sales values(5,'20011201', 1975.73)
    insert Sales values(5,'20030306', 178965.63)
    insert Sales values(6,'20020103', 16785.34)
    insert Sales values(6,'20030304', 1705.44)
    GO

     

     

    CREATE PROCEDURE sp_CrossTab_PIVOT
      @table       AS sysname,
      @onrows      AS nvarchar(128),
      @onrowsalias AS sysname = NULL,
      @oncols      AS nvarchar(128),
      @sumcol      AS sysname = NULL
    AS
    SET NOCOUNT ON
    DECLARE
      @sql AS nvarchar (max),
      @case AS varchar(1000)
    SET @case=''
    SELECT @sql='
    SELECT @case=@case+''[''+CONVERT(VARCHAR, '+@oncols+')+''], '''+
    ' FROM '+@table+' GROUP BY '+@oncols+' ORDER BY '+@oncols
     
    EXEC sp_executesql @sql,N'@case varchar(1000) out', @case=@case out
    SET @case=LEFT(@case, LEN(@case)-1)

    SELECT @sql='SELECT * FROM (
    SELECT '+@oncols+' y, '+@onrows+' '+@onrowsalias+', '+@sumcol+' FROM '+@table+
    ') as s
    PIVOT
    (SUM ('+@sumcol+') for y in ('+@case+')) as pv'
    PRINT @sql
    EXECUTE (@sql)

     


    EXEC sp_CrossTab_PIVOT
      @table       = 'Sales',
      @onrows      = 'MONTH(Date)',
      @onrowsalias = 'SaleMonth',
      @oncols      = 'YEAR(Date)',
      @sumcol      = 'Amount'
    GO


    http://www.t-sql.ru
    • Marked as answer by hope_011 Sunday, April 18, 2010 4:01 PM
    Sunday, April 18, 2010 1:41 PM
  • hi,

    what it is the data type of "attribute_value"  column? if it is varchar, then cast it to numeric type, like cast(isnull(attribute_value,'0') as int).


    Kiran (www.ggktech.com)
    • Marked as answer by hope_011 Sunday, April 18, 2010 4:01 PM
    Sunday, April 18, 2010 2:32 PM
  • hi,

    thanks alot everyone, it works fine with int values in attribute_value

    but what if the data was varchar? what aggregate function to use?

     

    thanks.


    If you need to convert it to a numeric type in order to do SUM() or AVG() or some kind of aggregate like that, then do what I posted earlier.

    If it is a VARCHAR type that does not have to be cast to a numeric type, then just use MIN() or MAX().

     


    --Brad (My Blog)
    • Marked as answer by hope_011 Sunday, April 18, 2010 4:00 PM
    • Marked as answer by hope_011 Sunday, April 18, 2010 4:01 PM
    Sunday, April 18, 2010 3:57 PM
    Moderator

All replies

  • CREATE TABLE Sales
    (
    SaleID int IDENTITY PRIMARY KEY CLUSTERED,
    ClientID int,
    Date datetime,
    Amount money
    )
    insert Sales values(1,'20010401', 15.48)
    insert Sales values(1,'20020302', 134.01)
    insert Sales values(1,'20031003', 2346.03)
    insert Sales values(2,'20030203', 754.88)
    insert Sales values(3,'20010301', 73.07)
    insert Sales values(3,'20030402', 734.46)
    insert Sales values(4,'20010301', 1567.10)
    insert Sales values(4,'20020404', 6575.70)
    insert Sales values(4,'20030307', 6575.77)
    insert Sales values(4,'20030309', 6575.37)
    insert Sales values(5,'20011201', 1975.73)
    insert Sales values(5,'20030306', 178965.63)
    insert Sales values(6,'20020103', 16785.34)
    insert Sales values(6,'20030304', 1705.44)
    GO

     

     

    CREATE PROCEDURE sp_CrossTab_PIVOT
      @table       AS sysname,
      @onrows      AS nvarchar(128),
      @onrowsalias AS sysname = NULL,
      @oncols      AS nvarchar(128),
      @sumcol      AS sysname = NULL
    AS
    SET NOCOUNT ON
    DECLARE
      @sql AS nvarchar (max),
      @case AS varchar(1000)
    SET @case=''
    SELECT @sql='
    SELECT @case=@case+''[''+CONVERT(VARCHAR, '+@oncols+')+''], '''+
    ' FROM '+@table+' GROUP BY '+@oncols+' ORDER BY '+@oncols
     
    EXEC sp_executesql @sql,N'@case varchar(1000) out', @case=@case out
    SET @case=LEFT(@case, LEN(@case)-1)

    SELECT @sql='SELECT * FROM (
    SELECT '+@oncols+' y, '+@onrows+' '+@onrowsalias+', '+@sumcol+' FROM '+@table+
    ') as s
    PIVOT
    (SUM ('+@sumcol+') for y in ('+@case+')) as pv'
    PRINT @sql
    EXECUTE (@sql)

     


    EXEC sp_CrossTab_PIVOT
      @table       = 'Sales',
      @onrows      = 'MONTH(Date)',
      @onrowsalias = 'SaleMonth',
      @oncols      = 'YEAR(Date)',
      @sumcol      = 'Amount'
    GO


    http://www.t-sql.ru
    • Marked as answer by hope_011 Sunday, April 18, 2010 4:01 PM
    Sunday, April 18, 2010 1:41 PM
  • Hello,

     Try below script:

    declare @cols varchar(1000)
    declare @query varchar(max)
    
    select @cols = ISNULL(@cols + ',[', '[') + extension_label + ']'
    from extention_attributes 
    where provider_ID = 1
    group by extension_label
    
    set @query = 'select customer_fname, ' + @cols + 
    'from '+
    '(select customer_fname, a.extension_ID, extension_label, attribute_value ' +
    'from customer ' + 
    'join extension_values as v on customer.customer_ID=v.customer_ID ' + 
    'join extension_attributes as a on a.extension_ID=v.extension_ID ' + 
    ')as srcTable ' +
    'pivot '+
    '(sum(attribute_value) for extension_label in ( ' + @cols + '))as pivotTable'
    
    print @query
    
    exec(@query)
    

    Kiran (www.ggktech.com)
    Sunday, April 18, 2010 1:52 PM
  • Hi,

     Try below script,

    declare @cols varchar(1000)
    declare @query varchar(max)
    
    select @cols = ISNULL(@cols + ',[', '[') + extension_label + ']'
    from extention_attributes 
    where provider_ID = 1
    group by extension_label
    
    set @query = 'select customer_fname, ' + @cols + 
    'from '+
    '(select customer_fname, a.extension_ID, extension_label, attribute_value ' +
    'from customer ' + 
    'join extension_values as v on customer.customer_ID=v.customer_ID ' + 
    'join extension_attributes as a on a.extension_ID=v.extension_ID ' + 
    ')as srcTable ' +
    'pivot '+
    '(sum(attribute_value) for extension_label in ( ' + @cols + '))as pivotTable'
    
    print @query
    
    exec(@query)
    

    Kiran (www.ggktech.com)
    Sunday, April 18, 2010 1:53 PM
  • hi, 

    i executed the above script, generated the following error:

    Msg 8117, Level 16, State 1, Line 1

    Operand data type varchar is invalid for sum operator.

    Sunday, April 18, 2010 2:18 PM
  • hi,

    what it is the data type of "attribute_value"  column? if it is varchar, then cast it to numeric type, like cast(isnull(attribute_value,'0') as int).


    Kiran (www.ggktech.com)
    • Marked as answer by hope_011 Sunday, April 18, 2010 4:01 PM
    Sunday, April 18, 2010 2:32 PM
  • hi,

    im filling it with int type, i performed casting like this:(sum(cast(attribute_value as int))

    i have this error:

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near '('

    but nevermind.

    the type of attribute_value must be varchar, for now im just filling it with sample data

    if this procedure worked fine with ints in attribute_value, what to do incase of varchar data types?? 


    Sunday, April 18, 2010 2:54 PM
  • hi,

    im filling it with int type, i performed casting like this:(sum(cast(attribute_value as int))

    i have this error:

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near '('

    but nevermind.

    the type of attribute_value must be varchar, for now im just filling it with sample data

    if this procedure worked fine with ints in attribute_value, what to do incase of varchar data types?? 



    CAST the Attribute_Value in the FROM clause...

    set @query = 'select customer_fname, ' + @cols + 
    'from '+
    '(select customer_fname, a.extension_ID, extension_label, AttValInt=CAST(attribute_value AS INT) ' +
    'from customer ' + 
    'join extension_values as v on customer.customer_ID=v.customer_ID ' + 
    'join extension_attributes as a on a.extension_ID=v.extension_ID ' + 
    ')as srcTable ' +
    'pivot '+
    '(sum(AttValInt) for extension_label in ( ' + @cols + '))as pivotTable'

     


    --Brad (My Blog)
    Sunday, April 18, 2010 3:35 PM
    Moderator
  • hi,

    thanks alot everyone, it works fine with int values in attribute_value

    but what if the data was varchar? what aggregate function to use?

     

    thanks.

    Sunday, April 18, 2010 3:48 PM
  • hi,

    thanks alot everyone, it works fine with int values in attribute_value

    but what if the data was varchar? what aggregate function to use?

     

    thanks.


    If you need to convert it to a numeric type in order to do SUM() or AVG() or some kind of aggregate like that, then do what I posted earlier.

    If it is a VARCHAR type that does not have to be cast to a numeric type, then just use MIN() or MAX().

     


    --Brad (My Blog)
    • Marked as answer by hope_011 Sunday, April 18, 2010 4:00 PM
    • Marked as answer by hope_011 Sunday, April 18, 2010 4:01 PM
    Sunday, April 18, 2010 3:57 PM
    Moderator