October 12, 2011 at 8:24 pm
I often use queries like:
SELECT *
FROM ThisTable
OUTER APPLY (SELECT (SELECT SomeField + ' ' AS [data()]
FROM SomeTable
WHERE SomeTable.ID = ThisTable.ID
FOR XML PATH ('')) AS ConcatenatedSomeField) A
But sometimes SomeTable is a large set of joined tables, and I actually want to get multiple concatenated fields from them. I would have to do this:
SELECT *
FROM ThisTable
OUTER APPLY (SELECT (SELECT SomeField + ' ' AS [data()]
FROM SomeTable
WHERE SomeTable.ID = ThisTable.ID
FOR XML PATH ('')) AS ConcatenatedSomeField) A
OUTER APPLY (SELECT (SELECT SomeField2 + ' ' AS [data()]
FROM SomeTable
WHERE SomeTable.ID = ThisTable.ID
FOR XML PATH ('')) AS ConcatenatedSomeField2) B
OUTER APPLY (SELECT (SELECT SomeField3 + ' ' AS [data()]
FROM SomeTable
WHERE SomeTable.ID = ThisTable.ID
FOR XML PATH ('')) AS ConcatenatedSomeField3) C
Which looks crappy and error prone, and could have performance implications re-getting the same tables over and over.
Is there a better way to get them as a set? I was thinking there might be some way to encode that OUTER APPLY into a simple XML format, and then extract what I want in the main SELECT statement outside. But I need some pointers to get me started, I haven't found anything I could follow easily enough yet.
Thanks.
October 13, 2011 at 3:31 am
I'm afraid I ended up overcomplicating the original problem.
Anyway, here's my solution:
-- SETUP: Create master table (sentences)
DECLARE @ThisTable TABLE (
id int
)
-- Create detail table (sentences broken in rows, Latin, English, Italian)
DECLARE @SomeTable TABLE (
id int,
rowid int,
Latin varchar(500),
English varchar(500),
Italian varchar(500)
)
INSERT INTO @ThisTable VALUES(1)
INSERT INTO @ThisTable VALUES(2)
INSERT INTO @ThisTable VALUES(3)
INSERT INTO @SomeTable VALUES(1, 1, 'Neque porro quisquam est, ',
'Nor again is there anyone who ',
'Viceversa non vi è nessuno che ama, ')
INSERT INTO @SomeTable VALUES(1, 2, 'qui dolorem ipsum quia dolor sit amet, ',
'loves or pursues or desires to obtain pain ',
'insegue, vuol raggiungere il dolore in sé ')
INSERT INTO @SomeTable VALUES(1, 3, 'consectetur, adipisci velit, sed quia non numquam ',
'of itself, because it is pain, but because occasionally ',
'perché è dolore ma perché talvolta ')
INSERT INTO @SomeTable VALUES(1, 3, 'eius modi tempora incidunt ',
'circumstances occur in which ',
'capitano circostanze tali per cui ')
INSERT INTO @SomeTable VALUES(1, 3, 'ut labore et dolore magnam aliquam quaerat voluptatem. ',
'toil and pain can procure him some great pleasure. ',
'con il travaglio e il dolore si cerca qualche grande piacere. ')
INSERT INTO @SomeTable VALUES(2, 1, 'Ut enim ad minima veniam, ',
'To take a trivial example, ',
'Per venire a casi di minima importanza, ')
INSERT INTO @SomeTable VALUES(2, 2, 'quis nostrum exercitationem ullam corporis suscipit laboriosam, ',
'which of us ever undertakes laborious physical exercise, ',
'chi di noi intraprende un esercizio fisico faticoso ')
INSERT INTO @SomeTable VALUES(2, 3, 'nisi ut aliquid ex ea commodi consequatur? ',
'except to obtain some advantage from it? ',
'se non per ottenere da esso qualche vantaggio?')
INSERT INTO @SomeTable VALUES(3, 1, 'Quis autem vel eum iure reprehenderit qui in ea voluptate ',
'But who has any right to find fault with a man who chooses to enjoy a pleasure ',
'O chi può biasimare colui che decide di provare un piacere ')
INSERT INTO @SomeTable VALUES(3, 2, 'velit esse quam nihil molestiae consequatur, ',
'that has no annoying consequences, ',
'che non porta conseguenze negative, ')
INSERT INTO @SomeTable VALUES(3, 3, 'vel illum qui dolorem eum fugiat quo voluptas nulla pariatur?',
'or one who avoids a pain that produces no resultant pleasure?',
'o ch fugge quel dolore che non produce nessun piacere?')
-- SOLUTION
SELECT id, Latin, English, Italian
FROM (
SELECT ThisTable.id, lang, string
FROM @ThisTable AS ThisTable
OUTER APPLY (
SELECT *
FROM (
SELECT 'Latin'
UNION ALL SELECT 'English'
UNION ALL SELECT 'Italian'
) Languages (lang)
CROSS APPLY (
SELECT id, string = (
SELECT string + ' ' AS [data()]
FROM @SomeTable AS src
UNPIVOT ( string FOR lang IN (Latin, English, Italian) ) AS u
WHERE id = ThisTable.id
AND lang = Languages.lang
ORDER BY rowid
FOR XML PATH('')
)
) AS ca
) AS oa
) AS src
PIVOT ( MIN(string) FOR lang IN ([Latin],[English],[Italian])) AS p
Basically, first I UNPIVOT the columns to rows, then I concatenate using a dummy inline table (Languages) to keep the original columns separated. Then I PIVOT the rows to create the output columns.
Hope this helps (and is somehow readable)
Gianluca
-- Gianluca Sartori
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply