Dynamic Columns in SQL Query

Jan-2010Clear

Jan-2010Rejected

Feb-20101Clear

Feb-2010 Rejected

Question

Hi all.. I need help in forming a SQL Query.

My requirement is - I have the table as follows-

ID	Date	Status	Count	Company	Location
1	1-1-2010	Clear	1	D	E
2	2-3-2010	Clear	2	C	F
3	1-1-2010	Rejected	3	B	G
4	2-3-2010	Rejected	4	A	H

Now, I want the output as follows in the sql query -

I am not able to create dynamic columns in query.

Please Help..

Thanks a lot in advance....

Jignesh Trivedi · Answer

hi,

try...

Create table #temp
(ID int,
Company varchar(25),
Location varchar(25),
Date date,
Count int,
Status varchar(25))

insert into #temp values(1,'TCS','Mumbai','1-1-2010',1,'Clear'),
(2,'Zensar','Hydrabad','2-2-2010',2,'Rejected'),
(3,'Infosys','Pune','3-3-2011',4,'Clear'),
(4,'IBM','Pune','4-4-2011',3,'Clear'),
(5,'Microsoft','Singapore','5-5-2012',5,'Rejected'),
(6,'Google','US','6-6-2013',4,'Rejected')

declare @columnscsv varchar(MAX)
declare @Sumcolumnscsv varchar(MAX)
declare @Sumcolumnscsvho varchar(MAX)
DECLARE @sql varchar(MAX)
Select @columnscsv = COALESCE(@columnscsv + '],[','') + Datename(m,Date) + cast(year(Date) as varchar) + 'Clear],[' + Datename(m,Date) + cast(year(Date) as varchar) + 'Rejected'
from #temp
group by Date,year(Date)

Select @Sumcolumnscsvho = COALESCE(@Sumcolumnscsvho + '],0) + isnull([','') + Datename(m,Date) + cast(year(Date) as varchar) + 'Clear],0) + isnull([' + Datename(m,Date) + cast(year(Date) as varchar) + 'Rejected'
from #temp
group by Date,year(Date)

Select @Sumcolumnscsv = COALESCE(@Sumcolumnscsv + ',Sum([','') + Datename(m,Date) + cast(year(Date) as varchar) + 'Clear]) as ' + Datename(m,Date) + cast(year(Date) as varchar) + 'Clear,Sum([' + Datename(m,Date) + cast(year(Date) as varchar) + 'Rejected]) as ' + Datename(m,Date) + cast(year(Date) as varchar) + 'Rejected'
from #temp
group by Date,year(Date)

print 'isnull([' + @Sumcolumnscsvho + '],0) as Total'

SET @sql = '
SELECT Company, Location,Status,sum([' + @Sumcolumnscsv +', isnull([' + @Sumcolumnscsvho + '],0) as Total
FROM (
select Company,Location, Datename(m,Date) + cast(year(Date) as varchar) + Status as m,Count, Status from #temp
) as A
PIVOT
(
SUM(Count)
FOR m IN (' + '[' + @columnscsv + ']' + ')
)AS pivot1 GROUP BY
ROLLUP((Company, Location,Status,' + '[' + @columnscsv + ']' + '))'

--print @sql
exec(@sql)

hope this will help you.

Riddhi Valecha · Answer

Hi... Yes... That solution worked out ..... Thanks a lot........ Actually.... it was my mistake... I did a mistake in copying the logic.... Sorry for the trouble !!

Jignesh Trivedi · Answer

hi, below link solution didn't work ? http://www.c-sharpcorner.com/Forums/Thread/241077/dynamic-columns-in-sql-query-urgent-plzz-help-me-out.aspx

Riddhi Valecha · Answer

Hi...

Please help.... to get Column-wise total also....

Company	Location	April2013Clear	April2013Rejected	May2013Clear	May2013Rejected	TotalClear	TotalRejected
A	H	1	2	1	2	1+1=2	2+2=4
B	G	2	3	2	3	2+2=4	3+3=6
C	F	3	4	3	4	3+3=6	4+4=8
		6	9	6	9	6+4+2=12	4+6+8=18

PLZZ/..

Its Dam Urgent !!

Riddhi Valecha · Answer

Hi...

Yaar....1 last query.... Please help....

I have a table as below -

ID	Date1	Date-2	Count	Status
1	2013-1-1	2013-1-1	1	Clear
2	2013-1-1	2013-1-1	2	Rejected
3	2013-1-1	2013-3-3	3	Clear
4	2013-1-1	2013-1-1	4	Rejected

Now, I want the output as follows -

Month	TotalCount	TotalClearCount	TotalRejectedCount	Clearin12days	Clear after 12 days	Efficiency
Jan-2013	1+2+3+4=10	1+3 = 4 (Status = Clear)	2 + 4 = 6 (Status = Rejected)	1 (Date2-Date1 <= 12)	2+3+4 = 9	TotalCount/TotalClearCount * 100 i.e. 10/4*100

These are based on Date1 Column.

Total count for Jan-2013 1+2+3+4 = 10 (Based on Date1)

Clear Count = 1+3=4 and Rejected Count = 2+4=6 (Based on Status)

Clear in 12 days => Date2- Date1 <= 12. This is true only in record-1 i.e. ID=1. and Status = Clear.

Clear after 12 days => In rest of the rows, Date2-Date1 > 12.

Last Column => Efficiency. I want to calculate all these values in stored procedure itself...

Is it possible ?? Please help me out...

Thanks a ton....

Jignesh Trivedi · Answer

ok you want add new row at last and that containing sum of all row (vertical).

I thing CUBE and ROLLUP can help you...

Ok to do this you have to change the which is dynamically create column for you.
I think it is very difficult.

Riddhi Valecha · Answer

Hi...

I already have sum as a new column "Total".

I am looking for a query to get below output -

Company	Location	April2011Clear	April2011Rejected	May2011Clear	May2011Rejected	Total
A	H	1	8	9	6	1+8+9+6 = 48
B	G	2	7	0	5	2+7+0+5= 14
C	F	3	6	1	4	3+6+1+4= 14
D	E	4	5	2	3	4+5+2+3=14
Total		1+2+3+4 = 10	8+7+6+5= 26	9+0+1+2=12	6+5+4+3=18

Also,

I have 1 more query...

Suppose there are 2 dates columns say date-1 and date-2.

Now, I want to have the total days difference between 2 dates (which I can get by DateDiff() function in SQL).

My query is - I want the output in the following format -

Company	April2011Clear	ClearWithin12days	ClearAfter12days	Efficiency	Total
A	1	If (date2-date1) <= 12, total = 1 else total =0	If (date2-date1)> 12, total = 1 else total =0	Clear in 12 days/total * 100	1
B	2	5
C	3	6
D	4	5

Please help....

Thanks a ton in advance....

Jignesh Trivedi · Answer

hi, I think you are looking for the solution for horizontal sum right? so, isunll(column1,0) + isunll(column2,0) + ... +isunll(columnm,0) as totalH hope this will help you.

Riddhi Valecha · Answer

Hi...

It worked out.... Thanks a ton once again...... The issue was in the data.....

Hey...1 last query..... Any idea about how to get column-wise total ??

By executing this stored procedure, I get row-wise total.

I am trying to get column-wise total.

Any idea how to set that logic ??

Please share....

Thanks a ton in advance ....

Jignesh Trivedi · Answer

it must work... can you please share some example data? so that I can check this with temp table...

Riddhi Valecha · Answer

Hi...

I tried this query...

The date column have dates in 2013-Sep, Oct, Nov and Dec Months.

But, this query is giving me only NovemberClear, NovemberRejected, DecemberClear and DecemberRejected Columns...

I am not able to get Sep and OCt months...

Any other solution ??

Jignesh Trivedi · Answer

hi,

try...

declare @columnscsv varchar(MAX)
declare @Sumcolumnscsv varchar(MAX)
declare @Sumcolumnscsvho varchar(MAX)
DECLARE @sql varchar(MAX)
Select @columnscsv = COALESCE(@columnscsv + '],[','') + DateName(month,DateAdd(month,month(Date) , 0)-1) + cast(year(Date) as varchar) + 'Clear],[' + DateName(month,DateAdd(month,month(Date) , 0)-1) + cast(year(Date) as varchar) + 'Rejected'
from #temp
group by month(Date),year(Date)

Select @Sumcolumnscsvho = COALESCE(@Sumcolumnscsvho + '],0) + isnull([','') + DateName(month,DateAdd(month,month(Date) , 0)-1) + cast(year(Date) as varchar) + 'Clear],0) + isnull([' + DateName(month,DateAdd(month,month(Date) , 0)-1) + cast(year(Date) as varchar) + 'Rejected'
from #temp
group by month(Date),year(Date)

Select @Sumcolumnscsv = COALESCE(@Sumcolumnscsv + ',Sum([','') + DateName(month,DateAdd(month,month(Date) , 0)-1) + cast(year(Date) as varchar) + 'Clear]) as ' + DateName(month,DateAdd(month,month(Date) , 0)-1) + cast(year(Date) as varchar) + 'Clear,Sum([' +DateName(month,DateAdd(month,month(Date) , 0)-1) + cast(year(Date) as varchar) + 'Rejected]) as ' + DateName(month,DateAdd(month,month(Date) , 0)-1) + cast(year(Date) as varchar) + 'Rejected'
from #temp
group by month(Date),year(Date)

query to remove duplicate moth year combination

hope this will help you.

Riddhi Valecha · Answer

Hi..

No re.... The Solution DateName(month , DateAdd( month , month(Date) , 0 ) - 1 ) is not working......

I am still working on this query..... And I am also trying to get totals on the Horizontal Side..

I require Totals on both the sides naa.....

If you have any other solution... please help me out !!

Thanks in advance once again...

Jignesh Trivedi · Answer

hi Riddhi,

I am doing just group by Date... so it is return duplicate value... I think...

yes you can more do focus on below query to remove duplicate column..

Select @columnscsv = COALESCE(@columnscsv + '],[','') + Datename(m,Date) + cast(year(Date) as varchar) + 'Clear],[' + Datename(m,Date) + cast(year(Date) as varchar) + 'Rejected'
from #temp
group by Date,year(Date)

try to use DateName(month , DateAdd( month , month(Date) , 0 ) - 1 ) instead of Datename(m,Date) also

add month(Date) in group by clause and remove date from group by...

hope this will help you.

Riddhi Valecha · Answer

Sure... Sure.....

1 more query....

The Date Column values are not distinct.

The values in the Date column are repeating.

Because of this, I am getting multiple time the @columnscsv in the pivot query.

Any Solution to this also ?? If yes, please share...

Thanks a ton once again !!

Jignesh Trivedi · Answer

Please mark as answer if this post is help you.

Riddhi Valecha · Answer

Hi... Yes ... I got the totals on one-side... Thanks a ton...

Riddhi Valecha · Answer

Hi Jignesh...

Thanks a ton yaar..... Your procedure solved half of my problem......

The only thing pending is - Horizontal and Vertical Totals ..

I am trying out that logic only...

Thanks a ton once again !!!

Jignesh Trivedi · Answer

ok Are you try my solution? also share logic how we can create no of column base on input parameter?

Riddhi Valecha · Answer

HI...

No yaar.... That did not help me...

I have to creat the number of columns in the SQL Server Stored Procedure.

Not in the .cs file of the project.

I will pass a numeric value(say 10) as a parameter to the stored procedure.

And I want my sql query to generate that much of columns.

Please help.... Thanks a ton ...

Jignesh Trivedi · Answer

hi,

Here get total is some what difficult... and also you required Date as column, I think that generate wrong data.

does your previous post help you?
http://www.c-sharpcorner.com/Forums/Thread/240076/dynamic-columns-in-sql-query.aspx

Riddhi Valecha · Answer

Hi.....

I once again need help here in the stored procedure....

I want 2 Total Colums -

1)Total Column name as "ClearTotal". Here I need the Total count of all the clear months.
i.e January2013Clear + march2013Clear

2) Total Colummn name as "RejectedTotal". Here, I need the total count of all rejecyed months.
i.e. January2013Rejected + March2013Rejected.

Please help me out.... Its dam urgent.......

Please help !!

Thanks a ton in advance.....