Splitting the results into separate columns

Question

I need a view compatible SQL query to accomplish the following result (View 1).

Table 1:
--------------
ID |    Folder
--------------
1  |    foo
2  |    bar

Table 2:
-------------------------------------
ID |    Table1_ID | Name    |   Right
-------------------------------------
1  |    1         | fooUser |   W
2  |    2         | barUser |   R

View 1:
-------------------------------
Folder  |   fooUser  |  barUser
-------------------------------
foo     |     W      |
bar     |            |    R

I was only able to do so with custom functions, but I can only use this as storage procedures. I want to bind the result to a GridView. I hope anybody can help me out.

If you expect it to be dynamic when a third row is added to Table 2 (and expecting a new column to appear) you're not going to get it from a view (or any other object composable into larger queries) - the query within a view can only be a static query, and any particular static query always produces results with the same "shape" - a fixed number of columns, with the names and data types of those columns also fixed. — Damien_The_Unbeliever, Aug 01 '13 at 14:12
Surely I need dynamic data. I was aware that my attempt is impossible, because I didn't find any possible solution. Thank you for clearing that up for me. Dunno if I should create a new topic, but I'll ask anyway: Could the Entity Framework do the task for me? Or is creating a datatable at runtime the most common solution? — Bonzai, Aug 01 '13 at 15:08

score 2 · Answer 1 · answered Aug 01 '13 at 12:52

2

Anytime you want to have values converted into columns, use PIVOT. Here you will need a select:

SELECT Folder, [fooUser], [baruser]
FROM (SELECT t.Folder, tt.* FROM one t JOIN two tt ON t.ID = tt.Table1_ID) AS source
PIVOT
(
  MAX(Rightt)
  FOR Name IN (fooUser, barUser)
) AS PivotTable;

Apply it to CREATE VIEW ... AS and it should work.

SQLFiddle

answered Aug 01 '13 at 12:52

makciook

1,537
10
19

Thanks for your solution, but I forgot to mention that I'm talking about dynamic data. – Bonzai Aug 01 '13 at 15:14

Taryn · Answer 2 · 2013-08-01T15:34:07.333

2

You can use an aggregate function with a CASE expression to pivot the data from rows into columns:

select t1.folder,
  max(case when t2.name = 'fooUser' then [right] else '' end) fooUser,
  max(case when t2.name = 'barUser' then [right] else '' end) barUser
from table1 t1
inner join table2 t2
  on t1.id = t2.Table1_ID
group by t1.folder;

See SQL Fiddle with Demo.

If you need a dynamic solution, then you will need to use dynamic SQL:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Name) 
                    from table2
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT folder, ' + @cols + ' 
            from 
            (
              select t1.folder,
                t2.name, t2.[right]
              from table1 t1
              inner join table2 t2
                on t1.id = t2.Table1_ID
            ) x
            pivot 
            (
                max([right])
                for name in (' + @cols + ')
            ) p '

execute(@query);

See SQL Fiddle with Demo

edited Aug 01 '13 at 15:34

answered Aug 01 '13 at 14:10

Taryn

242,637
56
362
405

Because I need dynamic data, I cannot use your solution neither. But I would like to know the difference in speed between your solution and the one from @makciook – Bonzai Aug 01 '13 at 15:19
Problem is that i cannot use dynamic SQL as view and stored procedures as a datasource for my control. – Bonzai Aug 01 '13 at 15:41
@Bonzai There is no way to perform this dynamically without using dynamic SQL in a stored procedure. – Taryn Aug 01 '13 at 15:46
1

@Bonzai "I need dynamic data" ... "I can't use dynamic SQL" ... you are a walking contradiction. If you want dynamic columns you need dynamic SQL, or you need to change your requirements. – Aaron Bertrand Aug 01 '13 at 15:58
Thanks for confirming the statement from @Damien_The_Unbeliever. – Bonzai Aug 01 '13 at 16:12

score 0 · Answer 3 · answered Aug 01 '13 at 14:05

Just for fun, here is a stupid answer, but it works :) I recommend using PIVOT though @makciook.

select a.Folder,
       foo.[Right] fooUser,
       bar.[Right] barUser
  from one a
  left join two foo on a.ID = 1 and foo.ID = 1
  left join two bar on a.ID = 2 and bar.ID = 2

Splitting the results into separate columns

3 Answers3