CODEWITHSUNDEEP
sqlpivot
salman hussain
salman hussain

Reputation: 23

Display multiple rows values in a single row with multiple column

   GR_NO| Month_ID | Amount
     43 |  7 | $200.00
     43 |  8 | $300.00
     43 |  9 | $500.00

show should be like this

GR_NO | 7       |       8 |    9 |
43    | $200.00 | $300.00 |$500.00

Upvotes: 0

Views: 2968

Answers (2)

rjamesak
rjamesak

Reputation: 26

SELECT GR_NO, [7], [8], [9]
FROM
    (SELECT GR_NO, Month, Amount From Database.schema.TableName)
    AS SourceTable
PIVOT
(
    Sum(Amount)
FOR Month IN ( [7], [8], [9])
) AS PivotTable;
--<optional ORDER BY clause>;

I researched this answer by looking at pivot table info here: https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

Using MSSQL

Result

GR_NO   7         8         9
43      200.00    200.00    500.00

Upvotes: 1

Shawn
Shawn

Reputation: 4786

Since I don't know what type of SQL you are using, this example is in MS T-SQL 2014 flavor. It should be similar in most other types of SQL.

SQL Fiddle

First, we set up the test table and data.

MS SQL Server 2014 Schema Setup:

CREATE TABLE t1 ( GR_NO int, Month_ID int, Amount decimal(10,2) ) ;
INSERT INTO t1 ( GR_NO, Month_ID, Amount )
VALUES 
    ( 43, 7, 200.00 )
  , ( 43, 8, 300.00 )
  , ( 43, 9, 500.00 )
  , ( 44, 2, 100.00 )
  , ( 43, 2, 250.00 )  /* Out of cycle */
  , ( 43, 4, 10.00 )  /* Aggregate 1 */
  , ( 43, 4, 10.00 )  /* Aggregate 2 */
;

This is your Pivot Query:

SELECT p.GR_NO
  , p.[1] AS Jan
  , p.[2] AS Feb
  , p.[3] AS Mar
  , p.[4] AS Apr /* Look at aggregated example. */
  , p.[5] AS May
  , p.[6] AS Jun
  , p.[7] AS Jul
  , p.[8] AS Aug
  , p.[9] AS Sep
  , p.[10] AS Oct
  , p.[11] AS Nov
  , p.[12] AS Dec 
  /* After the pivot, columns come from p. */
FROM (
  SELECT GR_NO, Month_ID, Amount
  FROM t1
  /* Source table for pivoting data. */
) s
PIVOT (
  SUM(Amount) /* Has to be an aggregate function. */
  FOR Month_ID IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
  /* These are the fields you'd like to end up in the pivot. */
) p

Which gives us:

Results:

| GR_NO |    Jan | Feb |    Mar |    Apr |    May |    Jun |    Jul |    Aug |    Sep |    Oct |    Nov |    Dec |
|-------|--------|-----|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|
|    43 | (null) | 250 | (null) |     20 | (null) | (null) |    200 |    300 |    500 | (null) | (null) | (null) |
|    44 | (null) | 100 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |

https://www.techonthenet.com/sql_server/pivot.php

Upvotes: 0

Related Questions