SQLTeam.com | Weblogs | Forums

Combine results on one row


#1

This is what I am trying to figure out how to do it.
Thanks for any help!


#2
SELECT
	*
FROM
	YourTable
PIVOT
( SUM(Cost) FOR DataDate IN ([20151231],[20160131],[20160229]))P;

Instead of using SELECT *, you can enumerate the columns and alias them to what you wish them to be.


#3

Should I be able to replace > SUM(Cost) FOR DataDate IN ([20151231],[20160131],[20160229])
by > SUM(Cost) FOR DataDate IN (Select distinct DataDate from YourTable order by DataDate Desc)
,because the dates will be dynamic.


#4

UNPIVOT operator requires a list of static columns, so you cannot do it the way you described. You may be able to use dynamic SQL to unpivot. You will find examples if you google, for example here.


#5

Hello JamesK,

Thank you so much for you help. You could put me on the right track by suggesting that pivot thing.
What I did is basically a function that returns a varchar with all the distinct dates separated by a coma.
The sample I provided for the readers to help me was actually not the exact same situation but very close.
Here is the real stuff I created based on your first suggestions.

DECLARE @sql NVARCHAR( 2000 );
DEClARE @Dates VARCHAR(200);

SET @Dates = (Select [dbo].fn_Availables);

SET @sql = '
Select * from(
SELECT top 100 percent DataDate,CompanyOCN, NPA, HostCLLI, SwitchCLLI, SwitchingCentreArea, TotalNxx, TotalNumbers, Available
FROM (
SELECT top 100 percent CONVERT(VARCHAR(10), DataDate, 120) as DataDate, CompanyOCN, NPA, HostCLLI, SwitchCLLI,
SwitchingCentreArea, TotalNxx, TotalNumbers ,Available
FROM tbl_TnUtilizationReport order by HostCLLI, SwitchCLLI,CONVERT(VARCHAR(10), DataDate, 120) desc
) AS q
WHERE Npa LIKE ''%613%'' order by HostCLLI, SwitchCLLI,CONVERT(VARCHAR(10), DataDate, 120) desc ) as t1
Pivot(
SUM(t1.Available) For t1.DataDate in ('+@Dates+') )as t2'

EXEC ( @sql );

Again thanks a lot!