CASE in Group By Clause - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

CASE in Group By Clause

Author

Topic

learntsql

524 Posts

Posted - 2010-03-17 : 03:06:15

Hi All,
Could any body please tell me
How to use CASE Statement in GROUP BY Clause to select column name for grouping data dynamically?
Or any other ways?
TIA.

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2010-03-17 : 03:22:25

can you please post your requirements.Along with some data and expected output.

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-17 : 03:27:26

Post the code you used

Madhivanan

Failing to plan is Planning to fail

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2010-03-17 : 03:42:52

-- DECLARE A TEMP TABLE
CREATE TABLE #TestText
(
fldID INT,
txtfld VARCHAR(200),
qty1 INT,
qty2 INT
)

-- INSERT DUMMY RECORDS IN TEMP TABLE
INSERT INTO #TestText ( fldID, txtfld, qty1, qty2 )
SELECT 1,
'1,11,22,2,5,4,8,9,44,66,88',
25,
100
UNION ALL
SELECT 1,
'A,B,C,D,AA,AV,BB,AD',
20,
200
UNION ALL
SELECT 3,
'11,2,6,68',
10,
500
UNION ALL
SELECT 3,
'11,2,6,68',
15,
400

-- CHECK ALL INSERTED VALUES
SELECT *
FROM #TestText

-- Optional Grouping
DECLARE @groupfld VARCHAR(200)
DECLARE @querytext VARCHAR(1000)

SET @groupfld = 'fldID' -- OR SET IT TO txtfld
SET @querytext = 'SELECT ' + @groupfld
+ ',SUM(CASE WHEN fldID = 1 THEN qty1 ELSE qty2 END )AS TOTAL_QTY
FROM #TestText
GROUP BY ' + @groupfld

-- EXECUTE QUERY STRING
EXEC ( @querytext )

-- DROP TEMP TABLE
DROP TABLE #TestText

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-17 : 03:44:44

something like

GROUP BY CASE @param
WHEN value1 THEN field1
WHEN value2 THEN field2
...
END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

learntsql

524 Posts

Posted - 2010-03-17 : 03:54:49

Thank you All.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-17 : 04:27:24

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources