Sql Concatenation Query Help

August 5, 2013

[font=arial, sans-serif][size=3]
[color=#500050]

IF OBJECT_ID('tempdb..#Test') IS NOT NULL
DROP TABLE #Test

CREATE TABLE #Test
(
ID INT ,
Desc1 VARCHAR(25),
Code1 VARCHAR(25),
Desc2 VARCHAR(25),
Code2 VARCHAR(25),
Desc3 VARCHAR(25),
Code3 VARCHAR(25),
Desc4 VARCHAR(25),
Code4 VARCHAR(25),
Desc5 VARCHAR(25),
Code5 VARCHAR(25)

)

INSERT INTO #Test
( ID ,
Desc1 ,
Code1 ,
Desc2 ,
Code2 ,
Desc3 ,
Code3 ,
Desc4 ,
Code4 ,
Desc5 ,
Code5
)
VALUES ( 1 , -- ID - int
'ABCD' , -- Desc1 - varchar(25)
'123' , -- Code1 - varchar(25)
'EFGH' , -- Desc2 - varchar(25)
'9875' , -- Code2 - varchar(25)
'' , -- Desc3 - varchar(25)
'' , -- Code3 - varchar(25)
'' , -- Desc4 - varchar(25)
'' , -- Code4 - varchar(25)
'' , -- Desc5 - varchar(25)
'' -- Code5 - varchar(25)
)

INSERT INTO #Test
( ID ,
Desc1 ,
Code1 ,
Desc2 ,
Code2 ,
Desc3 ,
Code3 ,
Desc4 ,
Code4 ,
Desc5 ,
Code5
)
VALUES ( 2 , -- ID - int[/color][/size][/font][font=arial, sans-serif][size=3]
'AD' , -- Desc1 - varchar(25)[/size][/font][font=arial, sans-serif][size=3]
'10000' , -- Code1 - varchar(25)[/size][/font][color=#500050][font=arial, sans-serif][size=3]
'' , -- Desc2 - varchar(25)[/size][/font][/color][color=#500050][font=arial, sans-serif][size=3]

'' , -- Code2 - varchar(25)
'' , -- Desc3 - varchar(25)
'' , -- Code3 - varchar(25)
'' , -- Desc4 - varchar(25)
'' , -- Code4 - varchar(25)
'' , -- Desc5 - varchar(25)
'' -- Code5 - varchar(25)
)

[/size][/font][/color][font=arial, sans-serif][size=3]
SELECT ID,[/size][/font][color=#500050][font=arial, sans-serif][size=3]

Desc1 + ' (' + Code1 + ')' + ',' +
Desc2 + ' (' + Code2 + ')' + ',' +
Desc3 + ' (' + Code3 + ')' + ',' +
Desc4 + ' (' + Code4 + ')' + ',' +
Desc5 + ' (' + Code5 + ')' AS 'Description' FROM #Test

[/size][/font][/color][font=arial, sans-serif][size=3]
-- Ouptput Should be [/size][/font]
[font=arial, sans-serif][size=3]
--ID Description [/size][/font][font=arial, sans-serif][size=3]
--1 ABCD (123),EFGH (9875)[/size][/font][font=arial, sans-serif][size=3]
--2 AD (10000)[/size][/font]

August 5, 2013

August 5, 2013

which DB server? sSc_hidingsofa

August 5, 2013

[quote name='raja8112' timestamp='1375712768' post='1304067719']

[font=arial, sans-serif][size=3][color=#500050] IF OBJECT_ID('tempdb..#Test') IS NOT NULL
DROP TABLE #Test

CREATE TABLE #Test
(
ID INT ,
Desc1 VARCHAR(25),
Code1 VARCHAR(25),
Desc2 VARCHAR(25),
Code2 VARCHAR(25),
Desc3 VARCHAR(25),
Code3 VARCHAR(25),
Desc4 VARCHAR(25),
Code4 VARCHAR(25),
Desc5 VARCHAR(25),
Code5 VARCHAR(25)

)

INSERT INTO #Test
( ID ,
Desc1 ,
Code1 ,
Desc2 ,
Code2 ,
Desc3 ,
Code3 ,
Desc4 ,
Code4 ,
Desc5 ,
Code5
)
VALUES ( 1 , -- ID - int
'ABCD' , -- Desc1 - varchar(25)
'123' , -- Code1 - varchar(25)
'EFGH' , -- Desc2 - varchar(25)
'9875' , -- Code2 - varchar(25)
'' , -- Desc3 - varchar(25)
'' , -- Code3 - varchar(25)
'' , -- Desc4 - varchar(25)
'' , -- Code4 - varchar(25)
'' , -- Desc5 - varchar(25)
'' -- Code5 - varchar(25)
)

INSERT INTO #Test
( ID ,
Desc1 ,
Code1 ,
Desc2 ,
Code2 ,
Desc3 ,
Code3 ,
Desc4 ,
Code4 ,
Desc5 ,
Code5
)
VALUES ( 2 , -- ID - int[/color][/size][/font]
[font=arial, sans-serif][size=3] 'AD' , -- Desc1 - varchar(25)[/size][/font]
[font=arial, sans-serif][size=3] '10000' , -- Code1 - varchar(25)[/size][/font]
[color=#500050][font=arial, sans-serif][size=3] '' , -- Desc2 - varchar(25)[/size][/font][/color]

[color=#500050][font=arial, sans-serif][size=3] '' , -- Code2 - varchar(25)
'' , -- Desc3 - varchar(25)
'' , -- Code3 - varchar(25)
'' , -- Desc4 - varchar(25)
'' , -- Code4 - varchar(25)
'' , -- Desc5 - varchar(25)
'' -- Code5 - varchar(25)
)[/size][/font][/color]

[font=arial, sans-serif][size=3] SELECT ID,[/size][/font]

[color=#500050][font=arial, sans-serif][size=3] Desc1 + ' (' + Code1 + ')' + ',' +
Desc2 + ' (' + Code2 + ')' + ',' +
Desc3 + ' (' + Code3 + ')' + ',' +
Desc4 + ' (' + Code4 + ')' + ',' +
Desc5 + ' (' + Code5 + ')' AS 'Description' FROM #Test[/size][/font][/color]

[font=arial, sans-serif][size=3]-- Ouptput Should be [/size][/font]

[font=arial, sans-serif][size=3]--ID Description [/size][/font]
[font=arial, sans-serif][size=3]--1 ABCD (123),EFGH (9875)[/size][/font]
[font=arial, sans-serif][size=3]--2 AD (10000)[/size][/font]
[/quote]

What is the issue with your Query? result nuvvu ankunnattey vastundi kada, aah pakkana extra commas undadhda ? be clear

August 5, 2013

[quote name='pinky123' timestamp='1375713212' post='1304067756']
which DB server? sSc_hidingsofa
[/quote]
afdb

August 5, 2013

[quote name='pandemkodi' timestamp='1375715036' post='1304067887']
afdb sSc_hidingsofa
[/quote]
anduke error vastundi

August 5, 2013

[quote name='pinky123' timestamp='1375713212' post='1304067756']
which DB server? sSc_hidingsofa
[/quote]

why hiding.. edina dhairyam ga adugu.. nuvvu adigindi correct ye.. Ammugif

August 5, 2013

[quote name='kevkekaa' timestamp='1375715140' post='1304067895']
why hiding.. edina dhairyam ga adugu.. nuvvu adigindi correct ye.. Ammugif
[/quote]
S%Hi ok

August 5, 2013

[quote name='pandemkodi' timestamp='1375715036' post='1304067887']
afdb sSc_hidingsofa
[/quote]
[quote name='pinky123' timestamp='1375715102' post='1304067893']
anduke error vastundi
[/quote]

@3$% @3$% @3$%

August 5, 2013

nothing wrong in your Query, it should get

1 ABCD (123),EFGH (9875), (), (), ()
2 AD (10000), (), (), (), ()

since your other fields are nulls...did you include the use[databasename] at the top

August 5, 2013

[quote name='raja8112' timestamp='1375712768' post='1304067719']

[font=arial, sans-serif][size=3][color=#500050] IF OBJECT_ID('tempdb..#Test') IS NOT NULL
DROP TABLE #Test

CREATE TABLE #Test
(
ID INT ,
Desc1 VARCHAR(25),
Code1 VARCHAR(25),
Desc2 VARCHAR(25),
Code2 VARCHAR(25),
Desc3 VARCHAR(25),
Code3 VARCHAR(25),
Desc4 VARCHAR(25),
Code4 VARCHAR(25),
Desc5 VARCHAR(25),
Code5 VARCHAR(25)

)

INSERT INTO #Test
( ID ,
Desc1 ,
Code1 ,
Desc2 ,
Code2 ,
Desc3 ,
Code3 ,
Desc4 ,
Code4 ,
Desc5 ,
Code5
)
VALUES ( 1 , -- ID - int
'ABCD' , -- Desc1 - varchar(25)
'123' , -- Code1 - varchar(25)
'EFGH' , -- Desc2 - varchar(25)
'9875' , -- Code2 - varchar(25)
'' , -- Desc3 - varchar(25)
'' , -- Code3 - varchar(25)
'' , -- Desc4 - varchar(25)
'' , -- Code4 - varchar(25)
'' , -- Desc5 - varchar(25)
'' -- Code5 - varchar(25)
)

INSERT INTO #Test
( ID ,
Desc1 ,
Code1 ,
Desc2 ,
Code2 ,
Desc3 ,
Code3 ,
Desc4 ,
Code4 ,
Desc5 ,
Code5
)
VALUES ( 2 , -- ID - int[/color][/size][/font]
[font=arial, sans-serif][size=3] 'AD' , -- Desc1 - varchar(25)[/size][/font]
[font=arial, sans-serif][size=3] '10000' , -- Code1 - varchar(25)[/size][/font]
[color=#500050][font=arial, sans-serif][size=3] '' , -- Desc2 - varchar(25)[/size][/font][/color]

[color=#500050][font=arial, sans-serif][size=3] '' , -- Code2 - varchar(25)
'' , -- Desc3 - varchar(25)
'' , -- Code3 - varchar(25)
'' , -- Desc4 - varchar(25)
'' , -- Code4 - varchar(25)
'' , -- Desc5 - varchar(25)
'' -- Code5 - varchar(25)
)[/size][/font][/color]

[font=arial, sans-serif][size=3] SELECT ID,[/size][/font]

[color=#500050][font=arial, sans-serif][size=3] Desc1 + ' (' + Code1 + ')' + ',' +
Desc2 + ' (' + Code2 + ')' + ',' +
Desc3 + ' (' + Code3 + ')' + ',' +
Desc4 + ' (' + Code4 + ')' + ',' +
Desc5 + ' (' + Code5 + ')' AS 'Description' FROM #Test[/size][/font][/color]

[font=arial, sans-serif][size=3]-- Ouptput Should be [/size][/font]

[font=arial, sans-serif][size=3]--ID Description [/size][/font]
[font=arial, sans-serif][size=3]--1 ABCD (123),EFGH (9875)[/size][/font]
[font=arial, sans-serif][size=3]--2 AD (10000)[/size][/font]
[/quote]

do you even know what do you want, you cannot just ask for help and disapper.

August 5, 2013

[quote name='Azaad' timestamp='1375716269' post='1304068001']

do you even know what do you want, you cannot just ask for help and disapper.
[/quote]
poniley gussa kaku vadiley [img] http://www.herbalremedy.in/images/big/navratna-hair-oil.jpg[/img]

August 5, 2013

[font=arial, sans-serif][size=3]
Here is the answer :[/size][/font][font=arial, sans-serif][size=3]
;with CTE AS[/size][/font][font=arial, sans-serif][size=3]
( [/size][/font][font=arial, sans-serif][size=3]
SELECT ID,[/size][/font][font=arial, sans-serif][size=3]
CASE WHEN LEN(Desc1) > 0 THEN Desc1 + ' (' + Code1 + ')' + ',' ELSE '' END +[/size][/font][font=arial, sans-serif][size=3]
CASE WHEN LEN(Desc2) > 0 THEN Desc2 + ' (' + Code2 + ')' + ',' ELSE '' END +[/size][/font][font=arial, sans-serif][size=3]
CASE WHEN LEN(Desc3) > 0 THEN Desc3 + ' (' + Code3 + ')' + ',' ELSE '' END +[/size][/font][font=arial, sans-serif][size=3]
CASE WHEN LEN(Desc4) > 0 THEN Desc4 + ' (' + Code4 + ')' + ',' ELSE '' END +[/size][/font][font=arial, sans-serif][size=3]
CASE WHEN LEN(Desc5) > 0 THEN Desc5 + ' (' + Code5 + ')' + ',' ELSE '' END AS 'Description' FROM #Test[/size][/font][font=arial, sans-serif][size=3]
)[/size][/font][font=arial, sans-serif][size=3]
select ID, LEFT([Description],len(Description)-1) from CTE[/size][/font]

August 5, 2013

;
WITH CTE AS(
SELECT ID,

CASE WHEn (Desc1 <>'') THEN Desc1 + ' (' + Code1 + ')' + '' END AS DESC1,
CASE WHEn (Desc2 <>'') THEN ','+Desc2 + ' (' + Code2 + ')' + '' END AS DESC2,
CASE WHEn (Desc3 <>'') THEN ','+Desc3 + ',(' + Code3 + ')' + '' END AS DESC3,
CASE WHEn (Desc4 <>'')THEN ','+Desc4 + ' ,(' + Code4 + ')' + '' END AS DESC4,
CASE WHEn (Desc5 <>'')THEN ','+Desc5 + ' ,(' + Code5 + ')' END AS DESC5
FROM #Test)
--select * from CTE

SELECT ID,ISNULL(DESC1,'')+ISNULL(DESC2,'')+ISNULL(DESC3,'')+ISNULL(DESC4,'')+ISNULL(DESC5,'')
AS 'Description'
FROM CTE

Here is the result:

ID Description
1 ABCD (123),EFGH (9875)
2 AD (10000)

August 5, 2013

[quote name='gundugadu' timestamp='1375718651' post='1304068143']
;
WITH CTE AS(
SELECT ID,

CASE WHEn (Desc1 <>'') THEN Desc1 + ' (' + Code1 + ')' + '' END AS DESC1,
CASE WHEn (Desc2 <>'') THEN ','+Desc2 + ' (' + Code2 + ')' + '' END AS DESC2,
CASE WHEn (Desc3 <>'') THEN ','+Desc3 + ',(' + Code3 + ')' + '' END AS DESC3,
CASE WHEn (Desc4 <>'')THEN ','+Desc4 + ' ,(' + Code4 + ')' + '' END AS DESC4,
CASE WHEn (Desc5 <>'')THEN ','+Desc5 + ' ,(' + Code5 + ')' END AS DESC5
FROM #Test)
--select * from CTE

SELECT ID,ISNULL(DESC1,'')+ISNULL(DESC2,'')+ISNULL(DESC3,'')+ISNULL(DESC4,'')+ISNULL(DESC5,'')
AS 'Description'
FROM CTE

Here is the result:

ID Description
1 ABCD (123),EFGH (9875)
2 AD (10000)
[/quote]

I was not sure atanu ivi maatramey eliminate cheyyalani.. but good one

Sign In

Sql Concatenation Query Help

Recommended Posts

raja8112

pinky123

pinky123

Darling999

pandemkodi

pinky123

Terminator II

pinky123

Silver_mani

Azaad

Azaad

Darling999

raja8112

gundugadu

Darling999

Popular Now

Tell a friend

Most viewed in last 30 days

Activity