locked
Pivot RRS feed

  • Question

  • whether we can pivot dynamic table in sql ???


    can anyone help me out...
    Friday, March 22, 2013 11:41 AM

Answers

  • Hi,

    Example for dynamic pivoting

    --Example to get pivoting columns dynamically
    CREATE  TABLE TMP(DBName NVARCHAR(30),ID INT)
    INSERT INTO TMP SELECT 'SQL Server',1
    INSERT INTO TMP SELECT 'Oracle',2
    --Example for dynamic pivot
    DECLARE @Columns NVARCHAR(MAX)
            ,@query NVARCHAR(MAX)
    SELECT @Columns = STUFF(
     (SELECT  ', ' +'['+DBName+']' FROM
     (SELECT DBName FROM TMP ) AS T FOR XML PATH('')),1,2,'')
      
    SET @query = N'
    SELECT ' + @Columns + '
    FROM
    (
      SELECT DBName,ID FROM TMP
    )  i
    PIVOT
    (
      MAX(ID) FOR DBName IN ('
      + @Columns
      + ')
    )  j;';
    EXEC sp_executesql @query;
    --adding 1 more row & checking dynamic pivoting
    INSERT INTO TMP SELECT 'DB2',3
    --Dynamic pivot
    DECLARE @Columns NVARCHAR(MAX)
            ,@query NVARCHAR(MAX)
    SELECT @Columns = STUFF(
     (SELECT  ', ' +'['+DBName+']' FROM
     (SELECT DBName FROM TMP ) AS T FOR XML PATH('')),1,2,'')
      
    SET @query = N'
    SELECT ' + @Columns + '
    FROM
    (
      SELECT DBName,ID FROM TMP
    )  i
    PIVOT
    (
      MAX(ID) FOR DBName IN ('
      + @Columns
      + ')
    )  j;';
    EXEC sp_executesql @query;
    Refer - http://sathyadb.blogspot.in/2012/10/sql-server-dynamic-pivoting.html

    Thanks & Regards, sathya

    • Proposed as answer by SathyanarrayananS Saturday, March 23, 2013 3:54 PM
    • Marked as answer by Mike Yin Wednesday, March 27, 2013 5:56 PM
    Friday, March 22, 2013 1:26 PM

All replies

  • whether we can pivot dynamic table in sql ???


    what is dynamic table?

    Nitesh Rai- Please mark the post as answered if it answers your question

    Friday, March 22, 2013 12:00 PM
  • You can pivot any table you can query, provided there is a unique recordid and pivot column.
    Friday, March 22, 2013 1:18 PM
  • Hi,

    Example for dynamic pivoting

    --Example to get pivoting columns dynamically
    CREATE  TABLE TMP(DBName NVARCHAR(30),ID INT)
    INSERT INTO TMP SELECT 'SQL Server',1
    INSERT INTO TMP SELECT 'Oracle',2
    --Example for dynamic pivot
    DECLARE @Columns NVARCHAR(MAX)
            ,@query NVARCHAR(MAX)
    SELECT @Columns = STUFF(
     (SELECT  ', ' +'['+DBName+']' FROM
     (SELECT DBName FROM TMP ) AS T FOR XML PATH('')),1,2,'')
      
    SET @query = N'
    SELECT ' + @Columns + '
    FROM
    (
      SELECT DBName,ID FROM TMP
    )  i
    PIVOT
    (
      MAX(ID) FOR DBName IN ('
      + @Columns
      + ')
    )  j;';
    EXEC sp_executesql @query;
    --adding 1 more row & checking dynamic pivoting
    INSERT INTO TMP SELECT 'DB2',3
    --Dynamic pivot
    DECLARE @Columns NVARCHAR(MAX)
            ,@query NVARCHAR(MAX)
    SELECT @Columns = STUFF(
     (SELECT  ', ' +'['+DBName+']' FROM
     (SELECT DBName FROM TMP ) AS T FOR XML PATH('')),1,2,'')
      
    SET @query = N'
    SELECT ' + @Columns + '
    FROM
    (
      SELECT DBName,ID FROM TMP
    )  i
    PIVOT
    (
      MAX(ID) FOR DBName IN ('
      + @Columns
      + ')
    )  j;';
    EXEC sp_executesql @query;
    Refer - http://sathyadb.blogspot.in/2012/10/sql-server-dynamic-pivoting.html

    Thanks & Regards, sathya

    • Proposed as answer by SathyanarrayananS Saturday, March 23, 2013 3:54 PM
    • Marked as answer by Mike Yin Wednesday, March 27, 2013 5:56 PM
    Friday, March 22, 2013 1:26 PM
  • Table with varying attributes
    Saturday, March 23, 2013 7:16 AM
  • thank u

    Saturday, March 23, 2013 7:16 AM
  • thank u for the information...
    Saturday, March 23, 2013 7:18 AM