CODEWITHSUNDEEP
sqlsql-servert-sql
user1941432
user1941432

Reputation:

Creating Dynamic Column Names from Dynamic Row Number

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 :)

Upvotes: 2

Views: 478

Answers (2)

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

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

Upvotes: 1

Taryn
Taryn

Reputation: 247630

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

Upvotes: 1

Related Questions