April 20, 2015 at 10:13 am
Hi ,
I need to get the below data in one single row... How can I do so?
CREATE TABLE #tbl_data (
Region Varchar(25),
code_1 Varchar(20),
code_2 Varchar(20)
);
INSERT INTO #tbl_data (
Region,
code_1,
code_2
)
VALUES
( 'North America','AB', 'Null'),
( 'North America' ,'Null', 'BC')
--( NULL, 'BC');
--Drop table #tbl_data
Select * from #tbl_data
April 20, 2015 at 10:36 am
sharonsql2013 (4/20/2015)
Hi ,I need to get the below data in one single row... How can I do so?
CREATE TABLE #tbl_data (
Region Varchar(25),
code_1 Varchar(20),
code_2 Varchar(20)
);
INSERT INTO #tbl_data (
Region,
code_1,
code_2
)
VALUES
( 'North America','AB', 'Null'),
( 'North America' ,'Null', 'BC')
--( NULL, 'BC');
--Drop table #tbl_data
Select * from #tbl_data
What do you want for output? There are at least ways I can think of to interpret this and they are all very different in what they would produce.
_______________________________________________________________
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/
April 20, 2015 at 10:43 am
North America , AB ,BC is the expected output
April 20, 2015 at 10:47 am
I assume you meant to have a NULL and not the string literal "NULL" in your data. If you have the string literal you just need to use a NULLIF.
CREATE TABLE #tbl_data (
Region Varchar(25),
code_1 Varchar(20),
code_2 Varchar(20)
);
INSERT INTO #tbl_data (
Region,
code_1,
code_2
)
VALUES
( 'North America','AB', Null),
( 'North America' , Null, 'BC')
--( NULL, 'BC');
Select Region
, MAX(code_1) as Code_1
, MAX(code_2) as Code_2
from #tbl_data
group by Region
_______________________________________________________________
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/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply