SELECT
	DATE,
	SUM(amount),
	SUM(CASE WHEN SIGN = 'I' THEN 1 ELSE 0 END) AS [I],
	SUM(CASE WHEN SIGN = 'B' THEN 1 ELSE 0 END) AS [B],
	SUM(CASE WHEN SIGN = 'W' THEN 1 ELSE 0 END) AS [W],
	SUM(CASE WHEN SIGN = 'A' THEN 1 ELSE 0 END) AS [A]
FROM
	CTE
GROUP BY DATE
ORDER BY DATE

--Creating Table

Create Table Ex
(DATE varchar(30),
 AMOUNT	Float,
 SIGN Char(1) )


--Inserting Sample Data

Insert into Ex
Select 'Jan 06 2012', 448.32,	'I'
Union ALL
Select 'Jan 06 2012', 870.00,	'B'
Union ALL
Select 'Jan 09 2012', 576.96,	'W'
Union ALL
Select 'Jan 12 2012', 523.12,	'W'
Union ALL
Select 'Jan 13 2012', 1446.75,	'W'
Union ALL
Select 'Jan 13 2012', 870.00,	'B'
Union ALL
Select 'Jan 13 2012', 528.75,	'A'
Union ALL
Select 'Jan 16 2012', 185.62,	'W'
Union ALL
Select 'Jan 16 2012', 870.00,	'W'
Union ALL
Select 'Jan 16 2012', 319.20,	'B'
Union ALL
Select 'Jan 16 2012', 319.20,	'B'
Union ALL
Select 'Jan 16 2012', 870.00,	'W'
Union ALL
Select 'Jan 16 2012', 870.00,	'W'
Union ALL
Select 'Jan 16 2012', 870.00,	'W'
Union ALL
Select 'Jan 16 2012', 870.00,	'W'


--Query Using Case

Select DATE, SUM(Amount) As Amount,
Sum(Case When SIGN = 'I' Then 1 Else 0 End) As I,
Sum(Case When SIGN = 'B' Then 1 Else 0 End) As B,
Sum(Case When SIGN = 'W' Then 1 Else 0 End) As W,
Sum(Case When SIGN = 'A' Then 1 Else 0 End) As A
From Ex
Group By Date


--Static Pivot

Select a.DATE, Amount, [I], [B], [W], [A] From
(Select DATE, SUM(Amount) As Amount From Ex
Group By DATE) As a
JOIN
(Select Date, [I], [B], [W], [A] From Ex
 Pivot
 (Count(Amount) For Sign IN ([I], [B], [W], [A])) As pvt) As b on a.Date = b.Date


--Dynamic pivot

Declare @cols varchar(max), @sql varchar(max)
Declare @temp Table(Cols varchar(max) )
Insert Into @temp
Select Distinct SIGN From Ex
Select @cols = Coalesce(@cols + ', ', '') + Quotename(Cols) From @temp
Set @sql = 'Select a.DATE, Amount, '+@cols+' From
				(Select DATE, SUM(Amount) As Amount From Ex
				Group By DATE) As a
				JOIN
				(Select Date, '+@cols+' From Ex
				Pivot
				(Count(Amount) For Sign IN ('+@cols+')) As pvt) As b on a.Date = b.Date'
Execute (@sql)

quote:

---

Originally posted by pushp82

Thanks for the response guys... I applied the case functionality as it was easy to apply and was cost efficient as well against all other solutions.

Thanks
Pushkar

---