CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlpivot
Timur
Timur

Reputation: 43

How make pivot table with 2 tables and use Month columns names

Whe have 2 tables:

CREATE TABLE [dbo].[Clients](
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [FIO] [nvarchar](100) NULL,
        [HPhone] [nchar](10) NULL,
        [MPhone] [nchar](10) NULL,
     CONSTRAINT [PK_Clients] PRIMARY KEY CLUSTERED )

    CREATE TABLE [dbo].[Payment](
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [Paid] [decimal](18, 0) NOT NULL,
        [date] [date] NULL,
        [IDClient] [int] NULL,
     CONSTRAINT [PK_Payment] PRIMARY KEY CLUSTERED 
    )

How make sql query to build this data:

ID  FIO                January   February  March     April     May       etc
--- ------------------ --------- --------- --------- --------- --------- ---------
1    Jimm Salvan       200       200       200       200       NULL      etc
2    Bob Dillan        200       200       200       200       NULL      etc

Upvotes: 1

Views: 2261

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79979

Use the PIVOT table operator:

SELECT
  Id,
  FIO,
  [1] AS January,
  [2] AS Febrary,
  [3] AS March,
  [4] AS April,
  [5] AS May,
  ... etc
FROM
(
  SELECT 
    c.id,
    c.FIO,
    ISNULL(p.paid, 0) AS paid,
    MONTH(Date) AS month
  FROM clients AS c
  LEFT JOIN Payment AS p ON c.Id = p.Id
) AS t
PIVOT
(
  SUM(paid)
  FOR month IN([1], 
               [2], 
               [3], 
               [4], 
               [5]
               ... etc)
) AS p;

SQL Fiddle Demo

Edit: You can use the DATENAME(MONTH, Date) to get the month name directly as @AndriyM suggested in the comments below. Something like this:

SELECT
  *
FROM
(
  SELECT 
    c.id,
    c.FIO,
    ISNULL(p.paid, 0) AS paid,
    DATENAME(month, Date) AS monthname
  FROM clients AS c
  LEFT JOIN Payment AS p ON c.Id = p.Id
) AS t
PIVOT
(
  SUM(paid)
  FOR monthname IN(January, 
                   February,
                   March, 
                   April, 
                   May,
                   ...)
) AS p;

Like this.

Upvotes: 3

Related Questions