Columns to Rows - Group By count

I have a table which has many columns but need to query it as rows.
The UNION ALL does the trick
My question is when we do a GROUP BY, is there a way we can generate a
sequence number automatically for columns that are grouped by.
Please see query below.
i.e. If there are many rows for this group
GROUP BY REFERENCE_ID, RECORD_KEY, SEQ_NO
it should start from 1, increment by 1
E.g.
REF1, KEY1, 1 , 1 , EXCEPTION1
REF1, KEY1, 1 , 2 , EXCEPTION2
REF1, KEY1, 1 , 3 , EXCEPTION3
REF1, KEY1, 2 , 1 , EXCEPTION1
REF1, KEY1, 2 , 2 , EXCEPTION2
REF1, KEY1, 3 , 1 , EXCEPTION1
REF1, KEY1, 3 , 2 , EXCEPTION2
REF2, KEY3, 1 , 1 , EXCEPTION1
REF2, KEY3, 2 , 1 , EXCEPTION1
REF2, KEY3, 2 , 2 , EXCEPTION2
REF2, KEY3, 2 , 3 , EXCEPTION3
REF2, KEY3, 3 , 1 , EXCEPTION1
REF2, KEY3, 3 , 2 , EXCEPTION2
Thank you.
Chris
Query:
SELECT REFERENCE_ID, RECORD_KEY, SEQ_NO, EOB_EXCP FROM EXCEPTIONS WHERE
LTRIM(EOB_EXCP) IS NOT NULL
UNION ALL
SELECT REFERENCE_ID, RECORD_KEY, SEQ_NO, PRIC_DIS_EXCP FROM EXCEPTIONS
WHERE LTRIM( PRIC_DIS_EXCP) IS NOT NULL
UNION ALL
SELECT REFERENCE_ID, RECORD_KEY, SEQ_NO, LT_DIS_EXCP FROM EXCEPTIONS WHERE
LTRIM( LT_DIS_EXCP) IS NOT NULL
UNION ALL
SELECT REFERENCE_ID, RECORD_KEY, SEQ_NO, UM_DIS_EXCP FROM EXCEPTIONS WHERE
LTRIM( UM_DIS_EXCP) IS NOT NULL
UNION ALL
SELECT REFERENCE_ID, RECORD_KEY, SEQ_NO, SE_DIS_EXCP FROM EXCEPTIONS WHERE
LTRIM( SE_DIS_EXCP) IS NOT NULL
UNION ALL
SELECT REFERENCE_ID, RECORD_KEY, SEQ_NO, PCA_DIS_EXCP FROM EXCEPTIONS WHERE
LTRIM( PCA_DIS_EXCP) IS NOT NULL
UNION ALL
SELECT REFERENCE_ID, RECORD_KEY, SEQ_NO, CE_DIS_EXCP FROM EXCEPTIONS WHERE
LTRIM( CE_DIS_EXCP) IS NOT NULL
UNION ALL
SELECT REFERENCE_ID, RECORD_KEY, SEQ_NO, PENALTY_EXCP FROM EXCEPTIONS WHERE
LTRIM( PENALTY_EXCP) IS NOT NULL
UNION ALL
SELECT REFERENCE_ID, RECORD_KEY, SEQ_NO, RW_DIS_EXCP FROM EXCEPTIONS WHERE
LTRIM( RW_DIS_EXCP) IS NOT NULL
UNION ALL
SELECT REFERENCE_ID, RECORD_KEY, SEQ_NO, DISC_DIS_EXCP FROM EXCEPTIONS
WHERE LTRIM( DISC_DIS_EXCP) IS NOT NULL
UNION ALL
SELECT REFERENCE_ID, RECORD_KEY, SEQ_NO, SUP_DISC_EXCP FROM EXCEPTIONS
WHERE LTRIM( SUP_DISC_EXCP) IS NOT NULL
UNION ALL
SELECT REFERENCE_ID, RECORD_KEY, SEQ_NO, SD_DIS_EXCP FROM EXCEPTIONS WHERE
LTRIM( SD_DIS_EXCP) IS NOT NULL
GROUP BY REFERENCE_ID, RECORD_KEY, SEQ_NOHi Chris,
Can you elaborate on this. The DDL, sample, result with how the sequence
should be generated'|||Well I can elaborate:)
This is the same question posed a million times most recently in the thread:
microsoft.public.sqlserver.programming
Top 5 Quesiton
Friday, April 21, 2006 11:39 AM
I'm sure you can now do your thing:)
(Super easy in Rac but I won't steal your thunder:)
www.rac4sql.net
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:B4A92362-AD6F-4DC1-8EDD-4B0CED9E0E2A@.microsoft.com...
> Hi Chris,
> Can you elaborate on this. The DDL, sample, result with how the sequence
> should be generated'|||same question.. different context.. :)
Can you elaborate this
I'm sure you can now do your thing:)
(Super easy in Rac but I won't steal your thunder:)|||Hello,
You may (or may not) want to check out the Help file online @.
www.rac4sql.net/onlinehelp.asp
See:
Summarizing and Displaying Data
Working with Grouped Data
8. Obtaining the Top N Rows Using Summary Value(s)
Better yet post a challenge:)
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:84C0A77E-6BDD-4026-BC53-4A04E2D2D199@.microsoft.com...
> same question.. different context.. :)
> Can you elaborate this
> I'm sure you can now do your thing:)
> (Super easy in Rac but I won't steal your thunder:)
>|||There you go changing your name again - I was just getting used to the
last one. At least this one looks like a real-life name. :P
*mike hodgson*
http://sqlnerd.blogspot.com
Steve Dassin wrote:

>Hello,
>You may (or may not) want to check out the Help file online @.
>www.rac4sql.net/onlinehelp.asp
>See:
>Summarizing and Displaying Data
> Working with Grouped Data
> 8. Obtaining the Top N Rows Using Summary Value(s)
>Better yet post a challenge:)
>"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
>news:84C0A77E-6BDD-4026-BC53-4A04E2D2D199@.microsoft.com...
>
>
>|||All the better to add to your 'Other SQL Nerds' :P
Steve Dassin
http://racster.blogspot.com/
:)
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message news:%23bTZEAcaGHA.454
8@.TK2MSFTNGP04.phx.gbl...
There you go changing your name again - I was just getting used to the last
one. At least this one looks like a real-life name. :P
mike hodgson
http://sqlnerd.blogspot.com
Steve Dassin wrote:
Hello,
You may (or may not) want to check out the Help file online @.
www.rac4sql.net/onlinehelp.asp
See:
Summarizing and Displaying Data
Working with Grouped Data
8. Obtaining the Top N Rows Using Summary Value(s)
Better yet post a challenge:)
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:84C0A77E-6BDD-4026-BC53-4A04E2D2D199@.microsoft.com...
same question.. different context.. :)
Can you elaborate this
I'm sure you can now do your thing:)
(Super easy in Rac but I won't steal your thunder:)