November 30, 2015 at 7:36 am
Hello ,
I have a table like this in sql server 20018
ID | COLA | COLB | COLC |
---------------------------------
1001 | 1AAA | | |
1001 | | 2BBB | |
1001 | | | 3CCC |
I need the resultant output with query as below
ID | ALLCOL|
------------------
1001 | 1AAA;2BBB;3CCC |
Please help
November 30, 2015 at 7:46 am
Quick suggestion, use MAX and GROUP BY to eliminate the blanks and compress the rows into one row.
😎
USE tempdb;
GO
SET NOCOUNT ON;
;WITH SAMPLE_DATA(ID,COLA,COLB,COLC) AS
(
SELECT * FROM
(VALUES (1001,'1AAA',' ',' ')
,(1001,' ','2BBB',' ')
,(1001,' ',' ','3CCC')
) AS X(ID,COLA,COLB,COLC)
)
SELECT
ID
,MAX(COLA) AS COLA
,MAX(COLB) AS COLB
,MAX(COLC) AS COLC
FROM SAMPLE_DATA SD
GROUP BY SD.ID;
Results
ID COLA COLB COLC
----- ---- ---- ----
1001 1AAA 2BBB 3CCC
November 30, 2015 at 7:49 am
Yuck. I would advise against stuffing all these columns into a single column like this but it is easy to using aggregation.
select ID
, MAX(COLA) + ';' + MAX(COLB) + ';' + MAX(COLC) as AllCol
from YourTable
group by ID
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 30, 2015 at 7:56 am
Create table #tab(ID int , COLA char(5), COLB char(5) ,COLC char(5))
INSERT INTO #tab(ID ,COLA, COLB, COLC)
VALUES(1001,'1AAA' ,null, null), (1001 ,null,'2BBB', null),(1001,null,null, '3CCC')
SELECT
ID,
STUFF((SELECT ';' + coalesce(COLA, COLB, COLC)
FROM #tab tab1
WHERE tab1.ID = tab2.ID
FOR XML PATH('')), 1, 1, '') ALLCOL
FROM #tab tab2
GROUP BY tab2.ID
ORDER BY 1
Thanks And Regards
Vineet Bhargava
vineetbhargav@gmail.com
November 30, 2015 at 8:05 am
vineetbhargav (11/30/2015)
Create table #tab(ID int , COLA char(5), COLB char(5) ,COLC char(5))INSERT INTO #tab(ID ,COLA, COLB, COLC)
VALUES(1001,'1AAA' ,null, null), (1001 ,null,'2BBB', null),(1001,null,null, '3CCC')
SELECT
ID,
STUFF((SELECT ';' + coalesce(COLA, COLB, COLC)
FROM #tab tab1
WHERE tab1.ID = tab2.ID
FOR XML PATH('')), 1, 1, '') ALLCOL
FROM #tab tab2
GROUP BY tab2.ID
ORDER BY 1
This produces the same output as the code I posted. While this does demonstrate a great technique for creating delimited lists it is a bit overkill here. Also you should break the habit of ordering by ordinal position. You should use the column name instead.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 30, 2015 at 8:24 am
Sean Lange (11/30/2015)
vineetbhargav (11/30/2015)
Create table #tab(ID int , COLA char(5), COLB char(5) ,COLC char(5))INSERT INTO #tab(ID ,COLA, COLB, COLC)
VALUES(1001,'1AAA' ,null, null), (1001 ,null,'2BBB', null),(1001,null,null, '3CCC')
SELECT
ID,
STUFF((SELECT ';' + coalesce(COLA, COLB, COLC)
FROM #tab tab1
WHERE tab1.ID = tab2.ID
FOR XML PATH('')), 1, 1, '') ALLCOL
FROM #tab tab2
GROUP BY tab2.ID
ORDER BY 1
This produces the same output as the code I posted. While this does demonstrate a great technique for creating delimited lists it is a bit overkill here. Also you should break the habit of ordering by ordinal position. You should use the column name instead.
It's an overkill, unless you change the functionality and possibly return different results. Try changing a null value into anything else or adding a row to see what I mean.
CREATE TABLE #TEST(
ID int,
COLA char(4),
COLB char(4),
COLC char(4)
);
INSERT INTO #TEST
VALUES
(1001 , '1AAA', null , null ),
(1001 , null , '2BBB', null ),
(1001 , null , null , '3CCC' );
--This will only retain one value per column per id.
SELECT ID,
MAX( COLA) + ';' + MAX( COLB) + ';' + MAX( COLC) ALLCOL
FROM #TEST
GROUP BY ID;
--This will retain all values.
SELECT DISTINCT
ID,
STUFF((SELECT ISNULL( ';' + COLA, '') + ISNULL( ';' + COLB, '') + ISNULL( ';' + COLC, '')
FROM #TEST i
WHERE i.ID = t.ID
FOR XML PATH('')),1,1, '') ALLCOLL
FROM #TEST t
GO
DROP TABLE #TEST
November 30, 2015 at 8:31 am
Luis Cazares (11/30/2015)
Sean Lange (11/30/2015)
vineetbhargav (11/30/2015)
Create table #tab(ID int , COLA char(5), COLB char(5) ,COLC char(5))INSERT INTO #tab(ID ,COLA, COLB, COLC)
VALUES(1001,'1AAA' ,null, null), (1001 ,null,'2BBB', null),(1001,null,null, '3CCC')
SELECT
ID,
STUFF((SELECT ';' + coalesce(COLA, COLB, COLC)
FROM #tab tab1
WHERE tab1.ID = tab2.ID
FOR XML PATH('')), 1, 1, '') ALLCOL
FROM #tab tab2
GROUP BY tab2.ID
ORDER BY 1
This produces the same output as the code I posted. While this does demonstrate a great technique for creating delimited lists it is a bit overkill here. Also you should break the habit of ordering by ordinal position. You should use the column name instead.
It's an overkill, unless you change the functionality and possibly return different results. Try changing a null value into anything else or adding a row to see what I mean.
CREATE TABLE #TEST(
ID int,
COLA char(4),
COLB char(4),
COLC char(4)
);
INSERT INTO #TEST
VALUES
(1001 , '1AAA', null , null ),
(1001 , null , '2BBB', null ),
(1001 , null , null , '3CCC' );
--This will only retain one value per column per id.
SELECT ID,
MAX( COLA) + ';' + MAX( COLB) + ';' + MAX( COLC) ALLCOL
FROM #TEST
GROUP BY ID;
--This will retain all values.
SELECT DISTINCT
ID,
STUFF((SELECT ISNULL( ';' + COLA, '') + ISNULL( ';' + COLB, '') + ISNULL( ';' + COLC, '')
FROM #TEST i
WHERE i.ID = t.ID
FOR XML PATH('')),1,1, '') ALLCOLL
FROM #TEST t
GO
DROP TABLE #TEST
Trust me Luis that I understand the difference. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 30, 2015 at 8:56 am
Trust me Luis that I understand the difference. 😉
I know you do, but the OP or others might not. 😉
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply