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., Sep 03 '13 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, Sep 03 '13 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., Sep 03 '13 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. — , Sep 03 '13 at 22:36
@Angela, whenever you concatenate multiple values within a single column, you are creating a very poor design (disqualifies it from First Normal Form, see "atomic values"). http://stackoverflow.com/q/3653462/65223 I'm not entirely sure what you are doing with 'ROLE1', 'ROLE2', 'ROLE3', but it is almost certainly some database 101 fail as well. — KM., Sep 04 '13 at 11:50
@KM... You are wrong. You have a question that can have unknown amount of answers, so you can't designate columns. yet you need to display those values on a web based grid. So you either do a get for those questions for EVERY single DB row the grid produces, so you can concatenate the list in the C# code. That would be a crap load of calls to sql server to get each individual list or you do it at the database level. Please don't assume you know something about what my requirements are and try to pass of your bad sniffing as being right because you are not. — , Sep 08 '13 at 22:28
@KM Furthermore, I asked for help on something. And instead of helping me with what I ask you try to infer or tear me down as inferior. Knowing nothing of the design or requirements. Do us all a favor and stay on point with the questions. Anything else is a waste of time. — , Sep 08 '13 at 22:30
@Angela, you seem to know everything, even though (in your own words) you are only "pretending" to DBA Programmer. good luck! — KM., Sep 09 '13 at 17:28

score 1 · Accepted Answer · answered Sep 03 '13 at 18:32

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 03 '13 at 18:32

Taryn

242,637
56
362
405

Oh man Bluefleet, I love you! Thanks so much! That is exactly what I needed. I appreciate you taking the time to help :) – Sep 03 '13 at 22:39
@Angela You are welcome, happy to help push you in the right direction! – Taryn Sep 03 '13 at 22:40

score 1 · Answer 2 · answered Sep 03 '13 at 18:34

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 03 '13 at 18:34

Stuart Ainsworth

12,792
41
46

Creating Dynamic Column Names from Dynamic Row Number

2 Answers2