SQL Query

Question

Post reply

SQL Query

Swetha527

Old Hand

Points: 350
More actions
June 14, 2011 at 7:58 am

#242104

My stored proc returning table a below
Name Date Size
................................................................
AAAA 15/04/2011 3
BBBB 10/05/2011 8
CCCC 11/06/2011 2
But i need data as per below format.
Name 15/04/2011 10/05/2011 11/06/2011
.......................................................................
AAAA 3 0 0
BBBB 0 8 0
CCCC 0 0 2

Viewing 7 posts - 1 through 6 (of 6 total)

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

Thomas LeBlanc SSCrazy Eights Points: 8085 More actions · Answer 1

Look at books Online for Pivot table

Thomas LeBlanc, MVP Data Platform Consultant

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 2

Another option is to use a a Cross Tab Query:

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns[/url]

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

bopeavy SSCertifiable Points: 5869 More actions · Answer 3

rajesh.bathala
My stored proc returning table a below
Name Date Size
................................................................
AAAA 15/04/2011 3
BBBB 10/05/2011 8
CCCC 11/06/2011 2
But i need data as per below format.
Name 15/04/2011 10/05/2011 11/06/2011
.......................................................................
AAAA 3 0 0
BBBB 0 8 0
CCCC 0 0 2

This is a good place to get you started http://www.kodyaz.com/articles/t-sql-pivot-tables-in-sql-server-tutorial-with-examples.aspx.

There is a few examples on using pivot.

:cool:

"There are no problems! Only solutions that have yet to be discovered!"

ramakantshankar SSC Enthusiast Points: 139 More actions · Answer 4

Hi Rajesh,

I have created same scenario using the data discussed in your question as below:

CREATE TABLE PIVOTING(NAME VARCHAR(4),DATE DATE, SIZE INTEGER);

INSERT INTO PIVOTING

SELECT * FROM (

SELECT 'AAAA' NAME, CAST('04/15/2011' AS DATE) DATE , 3 SIZE

UNION

SELECT 'BBBB', CAST('05/10/2011' AS DATE), 8

UNION

SELECT 'CCCC', CAST('06/11/2011' AS DATE), 2)A;

Now for pulling the data in the format as required you can use following PIVOT SQL:

SELECT * FROM PIVOTING

PIVOT (MAX(SIZE) FOR DATE IN ([2011-04-15],[2011-05-10],[2011-06-11]))A;

The result set of above SQL is as follows:

Name2011-04-152011-05-102011-06-11

AAAA 3 NULL NULL

BBBB NULL 8 NULL

CCCC NULL NULL 2

You can modify the SQL as per your convenience. Also, you can use this SQL in your stored procedure.

Feel free to revert back in case of any Queries on rshankar@infocepts.com

Thanks and Regards,
Ramakant
---------------------------------------------------------------
Ramakant Shankar
InfoCepts | www.infocepts.com
Off: +91 712 224 5867 Ext 8388, +1 301 560 2591 Ext 8388
---------------------------------------------------------------

ramakantshankar SSC Enthusiast Points: 139 More actions · Answer 5

ramakantshankar (6/14/2011)
Hi Rajesh,
I have created same scenario using the data discussed in your question as below:
CREATE TABLE PIVOTING(NAME VARCHAR(4),DATE DATE, SIZE INTEGER);
INSERT INTO PIVOTING
SELECT * FROM (
SELECT 'AAAA' NAME, CAST('04/15/2011' AS DATE) DATE , 3 SIZE
UNION
SELECT 'BBBB', CAST('05/10/2011' AS DATE), 8
UNION
SELECT 'CCCC', CAST('06/11/2011' AS DATE), 2)A;
Now for pulling the data in the format as required you can use following PIVOT SQL:
SELECT * FROM PIVOTING
PIVOT (MAX(SIZE) FOR DATE IN ([2011-04-15],[2011-05-10],[2011-06-11]))A;
The result set of above SQL is as follows:
Name2011-04-152011-05-102011-06-11
AAAA 3 NULL NULL
BBBB NULL 8 NULL
CCCC NULL NULL 2
You can modify the SQL as per your convenience. Also, you can use this SQL in your stored procedure.
Feel free to revert back in case of any Queries on rshankar@infocepts.com

Hi Rajesh,

As you have requested, I have developed a dynamic Query which will convert all the Unique records in Date Column of the table from ROWS to COLUMNs. Hence you need not hard code the COLUMN names.

Below is the developed SQL. The below SQL uses the same table I have created in my last reply as Quoted above:cool::

SELECT DISTINCT

DATE

INTO #AllDates

FROM pivoting;

SELECT

*

FROM #AllDates;

DECLARE @CursorLoop INTEGER

DECLARE @TempMetrics VARCHAR(100)

DECLARE @Metrics VARCHAR(MAX)

DECLARE @sql VARCHAR(MAX)

SET @Metrics = ''

DECLARE cursor_metrics CURSOR FOR

SELECT

*

FROM #AllDates

OPEN cursor_metrics

SET @CursorLoop = 1

FETCH NEXT FROM cursor_metrics INTO @TempMetrics

WHILE @@FETCH_STATUS = 0

BEGIN

SET @Metrics = @Metrics + ',' + '[' + @TempMetrics + ']'

FETCH NEXT FROM cursor_metrics INTO @TempMetrics

END

CLOSE cursor_metrics

DEALLOCATE cursor_metrics

SET @CursorLoop = 0

SET @Metrics = SUBSTRING(@Metrics, 2, LEN(@Metrics))

PRINT @Metrics

SET @sql = 'SELECT * FROM PIVOTING

PIVOT (MAX(SIZE) FOR DATE IN (' + @Metrics + '))A'

EXEC (@SQL)

You can use this SQL in your Stored Procedure with Slight modifications in Table names and Variable names, if required.

Feel free to revert back in case of any Queries on rshankar@infocepts.com

Thanks and Regards,
Ramakant
---------------------------------------------------------------
Ramakant Shankar
InfoCepts | www.infocepts.com
Off: +91 712 224 5867 Ext 8388, +1 301 560 2591 Ext 8388
---------------------------------------------------------------

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 6

Or you can look into using Dynamic Cross Tabs:

Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs[/url]

Similar to how Cross Tabs outperform PIVOTs, a Dynamic Cross Tab process will outperform a Dynamic PIVOT process.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato