Display multiple rows values in a single row with multiple column

Question

   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

If you're only looking for month_id's 7, 8, and 9 use a case statement. Otherwise look into using a pivot — Simon
– Simon, Commented Oct 30, 2017 at 18:57
Which DBMS product are you using? Postgres? Oracle? "SQL" is just a query language, not the name of a specific database product. — user330315
– user330315, Commented Oct 30, 2017 at 18:58
techonthenet.com/sql_server/pivot.php <<< That's for T-SQL, but the concept is the same. — Shawn
– Shawn, Commented Oct 30, 2017 at 18:59

rjamesak · Accepted Answer · 2017-10-30 20:14:42Z

1

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

edited Oct 30, 2017 at 20:14

answered Oct 30, 2017 at 20:01

rjamesak

264 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Shawn · Accepted Answer · 2017-10-30 20:03:48Z

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

Collectives™ on Stack Overflow

Display multiple rows values in a single row with multiple column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related