Case logic Dynamic Execution in Sql server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Case logic Dynamic Execution in Sql server

Author

Topic

kond.mohan
Posting Yak Master

213 Posts

Posted - 2014-09-15 : 10:19:55

Hi,data is Existed below manner
activity , date1
ab 19-aug-2014
kt 19-aug-2014
zt 19-aug-2014
yu 19-aug-2014
my logic is
select
sum( case when activity = 'ab' then '1' else 0 end)
sum( case when activity = 'kt' then '1' else 0 end)
sum( case when activity = 'zt' then '1' else 0 end)
sum( case when activity = 'yu' then '1' else 0 end)
from table

my query is that we had 4 activity's now.when new acivity inserted my query should needs execute that acivity also mentioned manner.

is anybody knows how to make case logic dynamically in sql server

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-15 : 10:23:33

why not just:


select activity, count(date1) as date1_count
from table
group by activity

kond.mohan
Posting Yak Master

213 Posts

Posted - 2014-09-15 : 10:45:09

Hi Britton ,
thanks for response. but we need
IF i insert new activity that column need to show at the column level
dynamically.

mohan

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-15 : 11:55:50

That's what my query does! Did you try it?

You can pivot the results to get columns

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2014-09-19 : 05:13:01

IF OBJECT_ID(N'tempdb..#Table') IS NOT NULL
DROP TABLE #Table

CREATE TABLE #Table (Id INT , Activity VARCHAR(30))
INSERT INTO #Table VALUES(1,'ab'),(1,'bc'),(1,'cd'),(1,'de'),(1,'ef')

DECLARE @Col VARCHAR(MAX),@SQL VARCHAR(MAX)

DECLARE @i INT,
@MaxCount INT

SELECT @MaxCount = MAX(cnt)
FROM (
SELECT ID,
COUNT(Activity) AS cnt
FROM #Table
GROUP BY Id
) X;

SET @i = 0;

WHILE @i < @MaxCount
BEGIN
SET @i = @i + 1;
SET @SQL = COALESCE(@Sql + ', ', '') + 'Activity' + cast(@i AS NVARCHAR(10));
END

SET @SQL = N';WITH CTE AS (
SELECT Id,CASE WHEN Activity = Activity THEN 1 ELSE 0 END AS Value, ''Activity''
+ CAST(row_number() OVER (PARTITION BY Id ORDER BY Id DESC) AS Varchar(10)) AS RowNo
FROM #Table)
SELECT *
FROM CTE
PIVOT (MAX(Value) FOR RowNo IN (' + @SQL + N')) pvt';

EXEC (@SQL)

Veera

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources