How to show the column wise data into row wise

  • Hi,

    i want to show the column data in row wise, can any one please help me how to write a query for this?

    assume data in table like below:

    Name Month Amount

    ------ ------ -------

    ABC Jun-08 100

    XYZ Feb-08 150

    AAA Mar-08 200

    ABC Feb-08 200

    XYZ Mar-08 100

    The output would be lie this:

    Name Jun-08 Feb-08 Mar-08

    ----- ------- ------- -------

    ABC 100 200 -

    XYZ - 150 100

    AAA - 200 -

    Please help me the query for the above.

    Thank you.

  • You can try the PIVOT operator, but you have to specify the columns that it will pivot on, so it is not really dynamic...

    To get test data:

    CREATE TABLE sales(name nvarchar(20) , month nvarchar(20) , amount int )

    GO

    INSERT INTO sales

    SELECT

    'ABC', 'Jun-08', 100

    UNION ALL

    SELECT

    'XYZ', 'Feb-08', 150

    UNION ALL

    SELECT

    'AAA', 'Mar-08', 200

    UNION ALL

    SELECT

    'ABC', 'Feb-08', 200

    UNION ALL

    SELECT

    'XYZ', 'Mar-08' , 100

    --THEN:

    SELECT

    *

    FROM

    (SELECT name, month, Amount FROM sales ) S

    PIVOT (SUM(amount) FOR month IN ([Feb-08],[Mar-08], [Jun-08])) AS PIVOTCOLUMNS

    ORDER BY name

    --OR

    SELECT

    PIVOTCOLUMNS.name ,

    PIVOTCOLUMNS.[Feb-08],

    PIVOTCOLUMNS.[Mar-08],

    PIVOTCOLUMNS.[Jun-08]

    FROM sales S

    PIVOT (SUM(amount) FOR month IN ([Feb-08],[Mar-08], [Jun-08])) AS PIVOTCOLUMNS

    ORDER BY

    PIVOTCOLUMNS.name

  • Many thanks for your kind reply to my query..

    but.. please advise me, how to proceed if i have N number of records in my table:

    Name Month Amount

    ------ ------ -------

    ABC Jun-08 100

    XYZ Feb-08 150

    AAA Mar-08 200

    ABC Feb-08 200

    XYZ Mar-08 100

    . . .

    . . .

    . . .

    . . .

    N N N

    Thank you once again.:)

  • i mean to say that.. month will not change (Jun-08,Feb-08,Mar-08) but other details Name and amount would be N times.

    Name Month Amount

    ------ ------ -------

    ABC Jun-08 100

    XYZ Feb-08 150

    AAA Mar-08 200

    ABC Feb-08 200

    XYZ Mar-08 100

    . . .

    . . .

    . . .

    . . .

    N N N

  • The following works for me. It uses dynamic sql: (I am all for a simpler solution if anybody else has one!)

    DECLARE @MONTHS NVARCHAR(MAX)

    SELECT @MONTHS = ''

    SELECT @MONTHS = @MONTHS + '[' + month + '],'

    FROM (SELECT DISTINCT month FROM sales) S

    SELECT @MONTHS = SUBSTRING(@MONTHS,1,LEN(@MONTHS) - 1 )

    DECLARE @SQLSTR NVARCHAR(MAX)

    SELECT @SQLSTR =

    'SELECT

    PIVOTCOLUMNS.*

    FROM sales S

    PIVOT (SUM(amount) FOR month IN (' + @MONTHS + ')) AS PIVOTCOLUMNS

    ORDER BY

    PIVOTCOLUMNS.name'

    exec sp_executesql @SQLSTR

  • Also asked and answered here

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=107310


    N 56°04'39.16"
    E 12°55'05.25"

  • I always recommend doing pivot operations in a front-end application, like Excel or Reporting Services, instead of in SQL. They do it better, more easily, and faster.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • [p]Ooh Fun. A pivot! [/p]

    [p]This is the old-fashioned way I usually do it. I kept your date order just in case you needed it that way. The code looks slightly horrid but it is dead fast. I did the totals just like you'd see in Excel just to show off.[/p]

    [p] I know from experience that GSquared is always right, but where you are having to aggregate a million or more rows to create a pivot report, I'd have thought SQL starts to get the yellow jersey. It would be interesting to test it out.[/p]

    [font="Courier New"]

    CREATE TABLE sales([name] NVARCHAR(20) ,

                MONTH NVARCHAR(20) ,

                amount INT )

    GO

    INSERT INTO sales

    SELECT 'ABC', 'Jun-08', 100

    UNION ALL SELECT 'XYZ', 'Feb-08', 150

    UNION ALL SELECT 'AAA', 'Mar-08', 200

    UNION ALL SELECT 'ABC', 'Feb-08', 200

    UNION ALL SELECT 'XYZ', 'Mar-08' , 100

    UNION ALL SELECT 'ABA', 'Jun-08', 200

    UNION ALL SELECT 'ACC', 'Feb-08', 200

    UNION ALL SELECT 'XXX', 'Mar-08' , 100

    SELECT [name] =COALESCE([name],'Sum'),

           [Jun-08]=SUM(CASE WHEN MONTH='Jun-08'

                               THEN amount ELSE 0 END),

           [Feb-08]=SUM(CASE WHEN MONTH='Feb-08'

                               THEN amount ELSE 0 END),

           [Mar-08]=SUM(CASE WHEN MONTH='Mar-08'

                               THEN amount ELSE 0 END),

           [total]=SUM(amount)

    FROM sales GROUP BY [name] WITH ROLLUP

    ORDER BY GROUPING([name]),name

    /*

    result

    name                 Jun-08      Feb-08      Mar-08      total

    -------------------- ----------- ----------- ----------- -----------

    AAA                  0           0           200         200

    ABA                  200         0           0           200

    ABC                  100         200         0           300

    ACC                  0           200         0           200

    XXX                  0           0           100         100

    XYZ                  0           150         100         250

    Sum                  300         550         400         1250

    (7 row(s) affected)*/

    [/font]

    Best wishes,
    Phil Factor

  • thanks for your query and definitely its useful. BUT we can insert one by one record using union all to the created table. can we put bulk records instead inserting each record ? i mean can we make all columns arrays to insert all records in the table.

  • Sorry, I'm not sure if I understand the question. The UNION ALL statements were just there to insert the sample data into the test table. You'd use all sorts of techniques to get the data into the table: it wouldn't matter.

    Best wishes,
    Phil Factor

  • Phil, here's why I recommend pivoting data outside of SQL.

    Using your sample, I inserted 1-million rows of data into the same table structure you used.

    Then I added three more months of data.

    Your query, as written, was faster than Excel when it came to querying the data. Four seconds faster. But, with more months, it had to be rewritten to accomodate the extra columns.

    So I added the extra columns to the query. Still runs quite fast (approx 1 second). Excel took about 5 seconds, but I didn't have to rewrite anything. Just clicked the "refresh data" button on the toolbar.

    So I added another 3 months, which takes it into next year. Had to rewrite the query again. It's a little slower, takes just over 1 second now, on 1.5-million rows of data. Excel still taking about 5 seconds, but again, no code rewrite.

    It's not the speed. SQL is faster. But we're not talking about a difference of hours or even minutes here: it's a 3-4 second advantage.

    It's having to rewrite the query every month that I object to.

    And what happens when someone goes, "This darn query is 200 columns wide. I just want it to compare June of each year. How do I do that?" You, again, have to rewrite the query, or create a new report on top of a modified query. Excel? You click the little arrow next to the column list, and select the ones you want to display.

    And if the data were inserted as two columns instead of month and year in one column, Excel would require only that you select the specific month you want once, to filter a year-to-year comparison.

    I just don't like rewriting code every time some data changes or there's a slightly different need. Use a real interface that lets the user decide what they want and how they want it. More scalable.

    (By the way, thank you for the compliment.)

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Well, yes. Pivot tables are fiddly to do 'on the fly' in SQL Server I grant you. Also, you get free graphs, and drill-down if you use Excel. I quite often deliver reports as Excel pivot-tables myself where this seems the more appropriate way to do them. However, it is certainly possible to do nice Pivot tables in SQL Server, and as you've shown, they're quicker. I often have to deliver a lot of reports as Emails or update a lot of web-based daily reports, and, for this, SQL Server is ideal.

    I use routines similar to the stored procedure that Robyn Page and I did for Simple-Talk [/url]


    [font="Courier New"]CREATE PROCEDURE spDynamicCrossTab

    @RowValue VARCHAR(255),         --what is the SQL for the row title

    @ColValue VARCHAR(255),         --what is the SQL for the column title

    @Aggregate VARCHAR(255),        --the aggregation value to go in the cells

    @FromExpression VARCHAR(8000),              --the FROM, ON and WHERE clause

    @colOrderValue VARCHAR (255)=NULL,            --how the columns are ordered

    @Title VARCHAR(80)='_',    --the title to put in the first col of first row

    @SortBy VARCHAR(255)='row asc', --what you sort the rows by (column heading)

    @RowSort VARCHAR(80)=NULL,

    @ReturnTheDDL INT=0,--return the SQL code rather than execute it

    @Debugging INT=0    --debugging mode

    /*

    e.g.

    Execute spDynamicCrossTab

        @RowValue='firstname+'' ''+lastname',

        @ColValue='Year(OrderDate)',

        @Aggregate= 'count(*)',

        @FromExpression='FROM Employees INNER JOIN Orders

        ON (Employees.EmployeeID=Orders.EmployeeID)',

        @ColOrderValue='Year(OrderDate)',

       @Title ='No. Sales per year',

       @SortBy ='total desc' --what you sort the rows by (column heading)

    Execute spDynamicCrossTab

        @RowValue='firstname+'' ''+lastname',

        @ColValue='DATENAME(month,orderDate)',

        @Aggregate= 'sum(subtotal)',

        @FromExpression='FROM Orders

       INNER JOIN "Order Subtotals"

           ON Orders.OrderID = "Order Subtotals".OrderID

       inner join employees on employees.EmployeeID =orders.EmployeeID',

        @ColOrderValue='datepart(month,orderDate)',

       @Title ='Customers orders per month '

    EXECUTE spDynamicCrossTab

        @RowValue='country',

        @ColValue='datename(quarter,orderdate)

         +case datepart(quarter,orderdate)

             when 1 then ''st''

             when 2 then ''nd''

             when 3 then ''rd''

             when 4 then ''th'' end',

        @Aggregate= 'sum(subtotal)',

        @FromExpression='FROM Orders

       INNER JOIN "Order Subtotals"

           ON Orders.OrderID = "Order Subtotals".OrderID

      inner join customers on customers.customerID =orders.customerID',

        @ColOrderValue='datepart(quarter,orderDate)',

       @sortby='total desc',

       @Title ='value of orders per quarter'

    */

    AS

    SET NOCOUNT ON

    DECLARE @Command NVARCHAR(MAX)

    DECLARE @SQL VARCHAR(MAX)

    --make sure we have sensible defaults for orders

    SELECT @ColOrderValue=COALESCE(@ColOrderValue, @ColValue),

       @Sortby=COALESCE(@SortBy,@RowValue),

        @rowsort=COALESCE(@RowSort,@RowValue)

    --first construct tha SQL which is used to calculate the columns in a

    --string

    SELECT @Command='select @SQL=coalesce(@SQL,''SELECT

      ['+@Title+']=case when row is null then ''''Sum''''

    else convert(Varchar(80),[row]) end ,

    '')+

      ''[''+convert(varchar(100),'

       +@ColValue+')+''] =sum( CASE col WHEN ''''''+convert(varchar(100),'

       +@ColValue+')+'''''' THEN data else 0 END ),

    '' '+@FromExpression+'

    GROUP BY '+@ColValue+'

    order by max('+@ColorderValue+')'

    --Now we execute the string to obtain the SQL that we will use for the

    --crosstab query

    EXECUTE sp_ExecuteSQL @command,N'@SQL VARCHAR(MAX) OUTPUT',@SQL OUTPUT

      IF @@error > 0 --display the string if there is an error

        BEGIN

          RAISERROR ( 'offending code was ...%s', 0, 1, @command )

          RETURN 1

        END

    IF @debugging <>0 SELECT @Command

    --we now add the rest of the SQL into the string

    SELECT @SQL=@SQL+'  [Total]= sum( data )

    from

       (select [row]='+@RowValue+',

               [col]='+@ColValue+',

               [data]='+@Aggregate+',

               [sort]=max('+@rowsort+')

    '+@FromExpression+'

        GROUP BY '+@RowValue+', '+@ColValue+'

    )f

    group by row with rollup

    order by grouping(row),'+@Sortby

    --and execute it

    IF @ReturnTheDDL<>0 SELECT @SQL ELSE EXECUTE (@SQL)

      IF @@error > 0

        BEGIN

          RAISERROR ( 'offending code was ...%s', 0, 1, @sql )

          RETURN 1

        END

    [/font]


    Then doing this chap's Pivot job is easy


    [font="Courier New"]EXECUTE spDynamicCrossTab

        @colValue='month',

        @rowValue='name',

        @Aggregate= 'sum(amount)',

        @Rowsort='name',

        @FromExpression='FROM sales',

        @ColOrderValue='month',

        @Title ='Customers orders per month ',

        @sortby='row'

    [/font]


    or you can do just a small range perfectly happily


    [font="Courier New"]EXECUTE spDynamicCrossTab

        @colValue='month',

        @rowValue='name',

        @Aggregate= 'sum(amount)',

       @Rowsort='name',

        @FromExpression='FROM sales where month in (''FEB-08'',''MAR-08'')',

        @ColOrderValue='month',

       @Title ='Customers orders per month ',

       @sortby='row' [/font]


    Yes, I know that you could easily use SQL Injection with this sort of thing if you were silly about where you used it or with your security, or data input checking. However, in the right place, it is a wonderful way of churning out reports!

    Best wishes,
    Phil Factor

  • Every tool has it's right place. For what you're talking about, I'd use dynamic SQL too (and probably crib it from your workbench - I use a lot of the stuff you two have posted). My statement about using Excel is a general suggestion, not an absolute.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Hi,

    I too need to transpose row data column wise but the Input Which I Have is

    name Jun-08 Feb-08 Mar-08 total

    -------------------- ----------- ----------- ----------- -----------

    AAA 0 0 200 200

    ABA 200 0 0 200

    ABC 100 200 0 300

    ACC 0 200 0 200

    XXX 0 0 100 100

    XYZ 0 150 100 250

    & I need the output other Way i.e.

    Name Month Amount

    ------ ------ -------

    ABC Jun-08 100

    XYZ Feb-08 150

    AAA Mar-08 200

    ABC Feb-08 200

    XYZ Mar-08 100

    Please Answer me how do i do this

  • [p]I hope this helps[/p]

    [font="Courier New"]

    -- we prepare the sample data and put it in a temp table

    SELECT  * INTO #sampleData

    FROM

    (SELECT 'AAA' AS name, 0 AS 'Jun-08' , 0 AS 'Feb-08', 200 AS 'Mar-08', 200 AS 'Total'

    UNION ALL SELECT 'ABA', 200, 0, 0, 200

    UNION ALL SELECT 'ABC', 100, 200, 0, 300

    UNION ALL SELECT 'ACC', 0, 200, 0, 200

    UNION ALL SELECT 'XXX', 0, 0, 100 ,100

    UNION ALL SELECT 'XYZ', 0, 150, 100, 250)f

    -- and now we simply do the rotation.

    SELECT name, CONVERT(DATETIME,'1 Jun 2008',113) AS 'Month', [Jun-08] AS amount FROM #sampleData WHERE [Jun-08]>0

    UNION ALL SELECT name, CONVERT(DATETIME,'Feb 2008',113), [Feb-08] FROM #sampleData WHERE [Feb-08]>0

    UNION ALL SELECT name, CONVERT(DATETIME,'Mar 2008',113), [Mar-08] FROM #sampleData WHERE [Mar-08]>0

    [/font]

    Best wishes,
    Phil Factor

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply