Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Concatenation of strings with condition check,,, Expand / Collapse
Author
Message
Posted Monday, December 3, 2012 4:40 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 28, 2014 1:09 AM
Points: 227, Visits: 715
hi Team,

Need your help.............

My table contains below columns with values.

ColA ColB ColC ColD
-----------------------------
Admin NULL NULL NULL
Market 101 256 258
Sales 205 125 NULL
Admin 256 258 548


and i want output like below...

ColA:Admin,
ColA:Market, ColB:101 ColC:256 ColD:258
ColA:Sales : ColB:205 ColC:125
ColA:Admin : ColB:256 ColC:258 ColD:548

Trying with below login....but not getting exact query...
SELECT @query1 =
IF EXISTS (SELECT 1 from Table where colA ='Admin')
BEGIN

select 'ColA : ' + rtrim(ColA) + '


need your help plz...
Post #1391849
Posted Monday, December 3, 2012 5:44 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 36,995, Visits: 31,521
You could use either CASE or ISNULL or COALESCE for such a thing. I used ISNULL in the following example. Also, you would do better for yourself if you provided your data as readily consumable data so people can test their answer before posting it. See the first link in my signature below for an article on how to do that correctly.

 SELECT RTRIM(
+ 'ColA:'+ISNULL(ColA,'')+', '
+ ISNULL('ColB:'+CAST(ColB AS VARCHAR(10))+' ','')
+ ISNULL('ColC:'+CAST(ColC AS VARCHAR(10))+' ','')
+ ISNULL('ColD:'+CAST(ColD AS VARCHAR(10))+' ','')
)
FROM
( --=== Similation of your table. Use your table name here
SELECT 'Admin',NULL,NULL,NULL UNION ALL
SELECT 'Market',101,256,258 UNION ALL
SELECT 'Sales',205,125,NULL UNION ALL
SELECT 'Admin',256,258,548
)d(ColA,ColB,ColC,ColD)
;



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1391869
Posted Monday, December 3, 2012 6:52 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 28, 2014 1:09 AM
Points: 227, Visits: 715
Hi thank U Very Much,

But small change..

If column B is NULL then ColC should not append to the string.

Please.....
Post #1391916
Posted Monday, December 3, 2012 7:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 28, 2014 1:09 AM
Points: 227, Visits: 715
Hi Team,


BEGIN
SELECT RTRIM(
+ISNULL(Col_Name,'')+' : '
+ISNULL(+CAST(ColB AS VARCHAR(10))+' ','')
+ISNULL(' ColC :'+CAST(ColC AS VARCHAR(10))+' ','')
) FROM table_name
END

--

if ColB is NULL then ColC should not append .


Please help....
Post #1391934
Posted Monday, December 3, 2012 7:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:41 PM
Points: 13,315, Visits: 12,182
Minnu (12/3/2012)
Hi Team,


BEGIN
SELECT RTRIM(
+ISNULL(Col_Name,'')+' : '
+ISNULL(+CAST(ColB AS VARCHAR(10))+' ','')
+ISNULL(' ColC :'+CAST(ColC AS VARCHAR(10))+' ','')
) FROM table_name
END

--

if ColB is NULL then ColC should not append .


Please help....


If I understand you correctly you just need to add a case in here. (note I also added some spacing so it is more legible)

SELECT RTRIM(
+ ISNULL(Col_Name, '') + ' : '
+ ISNULL(CAST(ColB AS VARCHAR(10)) + ' ', '')
+ case when ColB IS NULL then NULL else ISNULL(' ColC :' + CAST(ColC AS VARCHAR(10)) + ' ', '') end
) FROM table_name



_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1391942
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse