Creating Dynamic Column Names from Dynamic Row Number

Question

About me I am not a SQL DBA Programmer. I am pretending to be one. So, when it comes to designing complex queries I am a beginner/middle of the road depending on what it is.

So far this is what I have come up with as an example to show you what i like to do.

tried researching this and I have gotten this far.

What I want is the RowNumber to be dynamic based off two factors RecId, GroupedDataId.

Without getting whicked into what I am doing. I am hoping this simple example will illustrate it enough.

Factors Number of Roles is unknown. RecId can have multiple GroupedDataIds. GroupedDataIds can have only one record.

Output Desired

PCRID,GROuPedDataId, AnswerText, Question
1   1   Driver, Driver  ROLE1
1   2   Driver, Driver  ROLE2
1   33  Driver, Driver  ROLE3
2   48  Driver, Driver  ROLE1
2   55  Driver, Driver  ROLE2
3   32  Driver, Driver  ROLE1
3   33  Driver, Driver  ROLE2
4   109 Driver, Driver  ROLE1

Example created

Create Table #example
(
 RecId int,
 GroupedDataId int,
 Question varChar(50),
 AnswerText varchar(100)
)
INSERT INTO #example (RecId, GroupedDataId, Question, AnswerText)
SELECT 1, 1, 'ROLE', 'Driver, Driver'
UNION
SELECT 1, 2, 'ROLE', 'Driver, Driver'
UNION
SELECT 1, 33, 'ROLE', 'Driver, Driver'
UNION
SELECT 2, 55, 'ROLE', 'Driver, Driver'
UNION
SELECT 2, 48, 'ROLE', 'Driver, Driver'
UNION
SELECT 3, 32, 'ROLE', 'Driver, Driver'
UNION<BR>
SELECT 3, 33, 'ROLE', 'Driver, Driver'
UNION
SELECT 4, 109, 'ROLE', 'Driver, Driver'

SELECT RecId
, GroupedDataId
, AnswerText
, Question = 'ROLE' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT RecId), (SELECT GroupedDataId)) AS VARCHAR(max))
FROM #example

DROP TABLE #example
<P>

This is what I am getting. Notice the Role # doesn't start over on a new RecId,GroupedDataId grouping. I was hoping the order by would do that but it didn't

PCRID,GROuPedDataId, AnswerText, Question
1       1   Driver, Driver  ROLE1
1   2   Driver, Driver  ROLE2
1   33  Driver, Driver  ROLE3
2   48  Driver, Driver  ROLE4
2   55  Driver, Driver  ROLE5
3   32  Driver, Driver  ROLE6
3   33  Driver, Driver  ROLE7
4   109 Driver, Driver  ROLE8

Any help would be greatly appreciated...I have spent all day pretty much getting myself to this point where my data looks like the above example.

Thanks :)

please tell me you are not designing this with CSV (comma separated values) data in it, AnswerText column. if so, stop right now and redesign that ASAP, you will thank me. — KM.
– KM., Commented Sep 3, 2013 at 18:33
@KM. If this is a question/answer then the answer might include commas and this might not be a design flaw — Taryn
– Taryn, Commented Sep 3, 2013 at 18:35
@bluefeet, look at the OPs given code, where they insert into the temp table: #example, the AnswerText column may just be plain text with a comma in it, but it looks like CSV data to me. that is why I'm letting the OP know. Also, the whole `ROLE1', 'ROLE2', 'ROLE3' thing doesn't pass the smell test. I'll make a prediction: OP will eventually be asking how to parse out the numeric portion of their ROLE field. — KM.
– KM., Commented Sep 3, 2013 at 18:40
@km you need to get a new sniffer, as I am not an idiot. I am a software architect with 18 years of programming experience. But I am not a DBA programmer. The comma is representation of concatenation of multiple answers per one question. From a normalized database I have designed. The information is to be displayed in a web based grid. As I said I didn't want to get into my design of the DB or my complex query. Which is why I boiled it down to an example, as to not waste anyone's time. I hate to say this but your comment was not useful or helpful and a waste of my time. — user1941432
– user1941432, Commented Sep 3, 2013 at 22:36

Taryn · Accepted Answer · 2013-09-03 18:32:23Z

1

Your current query is very close, you are missing the partition by on the row_number() windowing function. This will allow the number to be reset to 1 each time the recId changes:

SELECT RecId
  , GroupedDataId
  , AnswerText
  , Question = 'ROLE' + CAST(ROW_NUMBER() OVER (partition by RecId
                                                 order by RecId, GroupedDataId) AS VARCHAR(max)) 
FROM example;

See SQL Fiddle with Demo

answered Sep 3, 2013 at 18:32

Taryn

249k57 gold badges374 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1941432 Over a year ago

Oh man Bluefleet, I love you! Thanks so much! That is exactly what I needed. I appreciate you taking the time to help :)

Taryn Over a year ago

@Angela You are welcome, happy to help push you in the right direction!

Stuart Ainsworth · Accepted Answer · 2013-09-03 18:34:05Z

1

Try using the PARTITION method of the ROW_NUMBER() function:

SELECT RecId
, GroupedDataId
, AnswerText
, Question = 'ROLE' + CAST(ROW_NUMBER() OVER (PARTITION BY RecID ORDER BY RecId, GroupedDataId) AS VARCHAR(max))
FROM #example

answered Sep 3, 2013 at 18:34

Stuart Ainsworth

12.9k44 silver badges46 bronze badges

Collectives™ on Stack Overflow

Creating Dynamic Column Names from Dynamic Row Number

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related