How can I create a month name as a column name for a given date range in sql?

Question

I have a data as below:

Table

country     date                value       
------------------------------------------------------
test1       5/1/2008            500
test1       5/7/2008            200 
test1       5/8/2008            300
test1       7/1/2008            100
test1       7/2/2008            100
test2       6/1/2008            100

And I want a result as below:

 Result
-----------
countryName          May-08         Jun-08      July-08
test1                1000             -          200
test2                 -              100

It might be complex query. Please select all records and maintain it programming way rather then SQL query... — Surendra Jnawali, Feb 26 '13 at 11:37
you could create put all the dynamic columns in a variable, and then make it as statement, and then EXEC your query — vikas, Feb 26 '13 at 11:37

score 1 · Accepted Answer · edited May 23 '17 at 12:16

1

This is adapted from T-SQL Pivot? Possibility of creating table columns from row values

You can see it working here: http://sqlfiddle.com/#!3/7b8c0/28

I think you might need to fiddle around with the column ordering

-- Static PIVOT
SELECT *
FROM (SELECT country,
      CONVERT(char(3), date, 0) + '-' +  
      RIGHT(CONVERT(varchar, YEAR(date)), 2) AS date,
      value
FROM country) AS D
PIVOT(SUM(value) FOR date IN([May-08],[Jun-08],[Jul-08])) AS P;
GO

-- Dynamic PIVOT
DECLARE @T AS TABLE(y INT NOT NULL PRIMARY KEY);

DECLARE 
@cols AS NVARCHAR(MAX),
@y    AS INT,
@sql  AS NVARCHAR(MAX)

SELECT @cols = STUFF(
(SELECT N',' + QUOTENAME(y) AS [text()]
FROM (SELECT DISTINCT CONVERT(char(3), date, 0) + '-' +  
      RIGHT(CONVERT(varchar, YEAR(date)), 2) AS y 
      FROM Country
     ) AS Y
ORDER BY y desc
FOR XML PATH('')),
1, 1, N'')

-- Construct the full T-SQL statement
-- and execute dynamically
SET @sql = N'SELECT *
FROM (SELECT country, CONVERT(char(3), date, 0) + ''-'' +  
      RIGHT(CONVERT(varchar, YEAR(date)), 2) AS date, value
FROM Country) AS D
PIVOT(SUM(value) FOR date IN(' + @cols + N')) AS P;'

EXEC sp_executesql @sql

edited May 23 '17 at 12:16

Community

1
1

answered Feb 26 '13 at 12:02

openshac

4,966
5
46
77

what is the meaning of 'FOR XML PATH('')), 1, 1, N'')'? – sejal patel Feb 26 '13 at 12:06
It's just a neat trick to concatenate the column names into a string. There's an article here: http://www.mahipalreddy.com/blog/?p=79 – openshac Feb 26 '13 at 12:14
one more question can we create a month name as in given a date reange ex if i given a date 5/1/2008 to 7/2/2008 then how can i create a month may,june and july month as column name? as like we create above – sejal patel Feb 26 '13 at 12:27
i mean if i not insert a last record of " 6/1/2008" this into table then after i should show output "test1 1000 - 200" like this,it is possible? – sejal patel Feb 26 '13 at 12:34
can i add a date range in (SELECT N',' + QUOTENAME(y) AS [text()] FROM (SELECT DISTINCT CONVERT(char(3), date, 0) + '-' + RIGHT(CONVERT(varchar, YEAR(date)), 2) AS y FROM Country ) AS Y in this query or any else i have to add a date range can u tell me pls? becoz my date range is not a fixed so column also not fixed – sejal patel Feb 27 '13 at 11:06

score 0 · Answer 2 · edited May 23 '17 at 12:21

0

You have to use a rather complex query for that, using a LOOP it think.

For creating dynamic column names look at this post: https://stackoverflow.com/a/10926106/1321564

With sql server you have some advantages: https://stackoverflow.com/a/5638042/1321564

edited May 23 '17 at 12:21

Community

1
1

answered Feb 26 '13 at 11:27

Benjamin M

23,599
32
121
201

How can I create a month name as a column name for a given date range in sql?

2 Answers2