SQL Server convert rows to dynamic columns from related tables

Question

I'm having trouble converting lines to dynamic columns, I have the following structure:

User (table default)

UserID  UserName
1       First User Name
2       Second User Name

UserRegion (relationship: User)

UserID  RegionID
1       1
1       2
1       3
2       1
2       2
2       3

RegionLanguage

RegionID  LanguageID  RegionName
1         1           Region Name 1
2         1           Region Name 2
3         1           Region Name 3
4         1           Region Name 4
5         2           Region Name 5
6         3           Region Name 6

My difficulty is to create the structure of dynamic columns in accordance with the return of RegionLanguage table. My "Where" clause is RegionLanguage.LanguageID = 1 and my "Case" for RegionLanguage.RegionName is 'Yes' if there is relationship between the user and region (table UserRegion).

Desired output is:

UserID  UserName           Region Name 1   Region Name 2   Region Name 3   Region Name 4
1       First User Name    Yes             Yes             Yes             No
2       Second User Name   Yes             Yes             Yes             No

I am using SQL Server 2008.

Use SQL Server PIVOT function. stackoverflow.com/questions/13707938/… — Ondipuli
– Ondipuli, Commented Sep 15, 2014 at 15:00

Tristan · Accepted Answer · 2014-09-15 17:22:27Z

Several options here. Depending on your requirements you can do the following:

Join the region table more then once. This is good if you only have 4 or so possible columns. Then summarize the data with some CASE logic. This probably the most simple way but possibly lacks features you need or is insufficient for the amount of possible comments leading to lots of copy pasting.

Pivot. Once you get the hang of it this is really nice but it can be a bit daunting to start with. Look up some examples and get familiar with the PIVOT command and you'le figure it out. Has more features and won't get you into copy past drama.

multiple subqueries. You probably don't want this, but it's possible.

Dynamic SQL. worse then previous and since there is an alternative you should use it anyway. Just listing it for completeness of the answer.

Here is a simple example of a pivot for this scenario:

select name
    , max(case when [Region Name 1] is null then 0 else 1 end) [Region Name 1]
    , max(case when [Region Name 2] is null then 0 else 1 end) [Region Name 2]
    , max(case when [Region Name 3] is null then 0 else 1 end) [Region Name 3]
    , max(case when [Region Name 4] is null then 0 else 1 end) [Region Name 4]
    , max(case when [Region Name 5] is null then 0 else 1 end) [Region Name 5]
    , max(case when [Region Name 6] is null then 0 else 1 end) [Region Name 6]

from 
(select [user].name, RegionName, RegionName r2 from (values 
    (1,'First User Name'),
    (2, 'Second User Name')) [user](id, name)
left join (values
    (1, 1),
    (1, 3),
    (1, 3),
    (2, 4),
    (2, 1),
    (2, 6))
UserRegion(UserID,  RegionID)
    on [user].id = UserRegion.UserID
left join (values
    (1 ,1, 'Region Name 1'),
    (2 ,1, 'Region Name 2'),
    (3 ,1, 'Region Name 3'),
    (4 ,1, 'Region Name 4'),
    (5 ,2, 'Region Name 5'),
    (6 ,3, 'Region Name 6'))
RegionLanguage(RegionID, LanguageID, RegionName)
    on UserRegion.RegionID = RegionLanguage.RegionID) sub

PIVOT
(
MAX(RegionName)
FOR RegionName IN ( [Region Name 1]
    , [Region Name 2]
    , [Region Name 3]
    , [Region Name 4]
    , [Region Name 5]
    , [Region Name 6]   )
) AS PivotTable
group by name

Your answer is very thorough and explanatory. I saw many options using PIVOT before posting the question. But not quite understand how to apply, because I found something with multiple dynamic columns. I have endless possibilities of columns, because they come from my select in the table RegionLanguage ... Please would show me an example applicable? I thought about sending a ready query from my web application, making the query region before and incorporating in the final query. But it would be a bad solution.

Collectives™ on Stack Overflow

SQL Server convert rows to dynamic columns from related tables

User (table default)

UserRegion (relationship: User)

RegionLanguage

Desired output is:

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

User (table default)

UserRegion (relationship: User)

RegionLanguage

Desired output is:

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related