October 14, 2002 at 4:28 am
Hi,
I am just wondering if it were possible to get the below result in a single query. My table has four rows like
1, 1, 1, ES
1, 1, 1, JE
2, 2, 2, IT
2, 2, 2, FR
Now I want to see the result like
1,1,1,ES-JE
2, 2, 2, IT-FR
to say I want to concatenate two or more row values in a single column by grouping on other columns.
Can I do this in a single statement.
Thanks & Regards
Mitra
October 14, 2002 at 12:26 pm
Not not really exactly like that in one query. However if the last column has a finite number of possible answers you could create a CASE statement to denote each on in a pivot style method. This would work as you subquery, the you would have an external query to format them as a single entity and a final query to cleanup the output (which could be done in the application but my example does here). This would be best. You could probably do a concatination of the pivoted data inside a single query but not sure if it would remain valid.
Something similar to this should do the trick as long as I got all my fields in order and the functions filled properly (sorry if fails just let me know what it says as I have no server here to test, should be a simple error in syntax or structure if anything and easy to troubleshoot).
SELECT col1, col2, col3, RTRIM(LEFT(ColOut, LEN(ColOut) - (CASE WHEN LEN(ColOut) < 1 THEN 1 ELSE 0 END))) as ColFinalOutput
FROM
(
SELECT col1, col2, col3,
ISNULL((CASE WHEN colES = 1 THEN 'ES -' ELSE NULL END),'') +
ISNULL((CASE WHEN colJE = 1 THEN 'JE -' ELSE NULL END),'') +
ISNULL((CASE WHEN colIT = 1 THEN 'IT -' ELSE NULL END),'') +
ISNULL((CASE WHEN colFR = 1 THEN 'FR -' ELSE NULL END),'') as ColOut
FROM (
SELECT col1, col2, col3
CASE WHEN COL4 = 'ES' THEN 1 ELSE 0 END AS ColES,
CASE WHEN COL4 = 'JE' THEN 1 ELSE 0 END AS ColJE,
CASE WHEN COL4 = 'IT' THEN 1 ELSE 0 END AS ColIT,
CASE WHEN COL4 = 'FR' THEN 1 ELSE 0 END AS ColFR
FROM
tbl_MY_DATA
GROUP BY
col1, col2, col3
) AS InnerQuery
) AS OuterQuery
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
October 15, 2002 at 8:36 am
If you just wanted one row you can use the feature of the update statement to update a local variable.
i.e
UPDATE myTable
@concat = CASE WHEN @concat iS NULL THEN col ELSE @concat + '-' + col
WHERE col1 = 1 and col2 = 1 and col3 = 1
so @concat would become
ES-JE
Not really what your after but thought I would let you know.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply