JOIN three tables and aggregate data from multiple rows for every DISTINCT row in separate column

Question

i have a table where one item is mapped with multiple items.

Key 1 | Key 2
1       2
1       5
1       6
1       4
1       8

I have another table like this

Key 1 | ShortKey1Desc
1       'Desc short'

i have one more table where i have data like this

Key 1 | Description
1       'Desc a'
1       'Desc c'
1       'Desc aa'
1       'Desc tt'

i need to write a sql query for my view where table would be generated like this

Key 1 | AllKeys2ForKey1 | AllDescriptionsForKey1           | ShortKey1Desc
1     | 2;5;6;4;8       | Desc a; Desc c; Desc aa; Desc tt | Desc short

Key 1 is a string type field so i need to join them table using that string key

what i'm trying is to create a view for comfortable data access. need to create a query what will not take ages. i already tried to do it with Functions but it takes ages for load.

any help on this one would be highly appreciated. thanks a lot

Take a look here stackoverflow.com/questions/9139472/concatenate-grouped-row — anouar.bagari, May 15 '12 at 09:17
possible duplicate of [How to use GROUP BY to concatenate strings in SQL Server?](http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server) — Pondlife, May 15 '12 at 09:27
1). Why do you *need* this? It's a SQL anti-pattern and you existing structure is actually the one I'd recommend. What is the USE-CASE that you have that requires the denormalised representation? 2). Can the keys (which are strings) or the descriptions ever contain the character `;`? If so, how do you propose ot deal with that? — MatBailie, May 15 '12 at 09:30
i need to present the data in the grid. so need to collect all the data from different tables in that structure what is presented in third table. so basically thats the answer for the question why i need it. if i understood that correctly. any help? thanks a lot guys. — Mindaugas, May 15 '12 at 09:36

score 1 · Answer 1 · answered May 15 '12 at 11:19

Assuming that you are unable to change the data structures to make a more efficient query, this will work:

--Populate sample data
SELECT 1 as key1,       2  as key2 INTO #tbl1
UNION ALL SELECT 1,       5 
UNION ALL SELECT 1,       6 
UNION ALL SELECT 1,       4 
UNION ALL SELECT 1,       8 

SELECT 1  as key1,     'Desc short' as shortkeydesc INTO #tbl2

SELECT 1   as key1,    'Desc a'  as [description] INTO #tbl3
UNION ALL SELECT 1,       'Desc c' 
UNION ALL SELECT 1,       'Desc aa' 
UNION ALL SELECT 1,       'Desc tt' 

--Combine data into semi-colon separated lists
SELECT 
key1
,STUFF(
    (
    SELECT
      ';' + CAST(t2.key2 AS VARCHAR(10))
    FROM #tbl1 t2
    WHERE t2.key1 = tbl1.key1
    FOR XML PATH('')
    ), 1, 1, ''
  )
,STUFF(
    (
    SELECT
      ';' + tbl2.shortkeydesc
    FROM #tbl2 tbl2
    WHERE tbl2.key1 = tbl1.key1
    FOR XML PATH('')
    ), 1, 1, ''
  )
,STUFF(
    (
    SELECT
      ';' + tbl3.[description]
    FROM #tbl3 tbl3
    WHERE tbl3.key1 = tbl1.key1
    FOR XML PATH('')
    ), 1, 1, ''
  )
FROM #tbl1 tbl1
GROUP BY tbl1.key1

this seems doesnt work if table if in different database. i try to use it with with tables in same server but in different database. DataBase.dbo.TableName is this the problem or it should work between different database and im doing something wrong — Mindaugas, May 15 '12 at 12:19
@Mindaugas you need to refer to each database with the 3 or 4 part name as shown [here](http://www.mssqltips.com/sqlservertip/1095/sql-server-four-part-naming/) and this should do the trick. — Dibstar, May 15 '12 at 12:24
thanks a lot that worked well. i got like 20 k entries in every table and it loads so slow. could you give any advice please. how to make that thing go faster? i'm going nuts over here actually. thanks indeed. — Mindaugas, May 15 '12 at 12:40

score 0 · Answer 2 · answered May 15 '12 at 09:45

to convert rows into one single result you will need to save values in a variable, below is sample code just to give you an idea

Declare @AllKeys2ForKey1 varchar(50)
set @AllKeys2ForKey1 = ''
SELECT @AllKeys2ForKey1 = @AllKeys2ForKey1  + cast([Key 2] as varchar(3)) + ','
  FROM [AllKeys2ForKey1Table] where [KEY 1] = 1


Declare @AllDescriptionsForKey1 varchar(100)
set @AllDescriptionsForKey1 = ''
SELECT @AllKeys2ForKey1 = @AllKeys2ForKey1  + [Description] + ','
  FROM [AllDescriptionsForKey1Table] where [KEY 1] = 1

Declare @ShortKey1Desc varchar(100)
set @ShortKey1Desc = ''
SELECT @ShortKey1Desc = @ShortKey1Desc  + [ShortKey1Desc] + ','
  FROM [ShortKey1DescTable] where [KEY 1] = 1  


Select [KEY 1], 
        substring(@AllKeys2ForKey1,1,len(@AllKeys2ForKey1) - 1) as 'AllKeys2ForKey1 ',
        substring(@AllDescriptionsForKey1,1,len(@AllDescriptionsForKey1) - 1) as 'AllDescriptionsForKey1',
        substring(@ShortKey1Desc,1,len(@ShortKey1Desc) - 1)  as 'ShortKey1Desc'
from Table where [KEY 1]= 1

score 0 · Answer 3 · answered May 21 '12 at 23:17

You Must Write CLR Aggregate Function for Solving This Question. for write CLR Aggregate Function : 1: Run Microsoft Visual Stadio 2: Create New Project 3: then Select Data Project 4: CLR Aggregate Function

After Create Your Aggregate Function Create Your Query Such as Below

 Select A.Key1, OwnAggregateFn(B.Description), OwnAggregateFn(C.Key2), ...
 From A
 inner join B ON B.Key1 = A.Key1
 inner join C ON C.Key1 = A.Key1
 ... 
 Group By A.Key1

JOIN three tables and aggregate data from multiple rows for every DISTINCT row in separate column

3 Answers3

Linked