SQL Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Query

Author

Topic

visa123
Yak Posting Veteran

54 Posts

Posted - 2010-11-26 : 09:22:21

In my table values

ShortState Division
CA 1
CA 2
CA 3
CA 4

SELECT * FROM Table1

Sometimes the values will come

ShortState Division
CA 1
CA 2

Here i have some condition

1,2,3,4 values is there i have to give 1 if its no value in table give 0

like CA,1,1,0,0

Below is the query.but i cant put case condition in this.

DECLARE @State VARCHAR(50)
SET @State ='CA'
Declare @OutputString varchar(max)
SELECT @OutputString = IsNull(@OutputString + ',' + Division , Division)
FROM
(

Select Distinct ShortState + ',' + Division as Division from Table1 where ShortState=@State

) tbl

PRINT @OutputString

Can anyone help me for this?

Visa.G

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-26 : 09:31:51

SELECT @OutputString = coalesce(@OutputString + ',','') + case when Division is not null then '1' else '0' end
from

or maybe you want
DECLARE @State VARCHAR(50)
SET @State ='CA'
Declare @OutputString varchar(max)
SELECT @OutputString = coalesce(@OutputString + ',','') + case when Division is not null then '1' else '0' end
FROM
(
select
(Select i = 1 union all select 2 union all select 3 union all select 4) a
left join
Table1 t
on t.division = a.i
and ShortState=@State
) tbl

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visa123
Yak Posting Veteran

54 Posts

Posted - 2010-11-26 : 09:50:02

Hi

thanks for your reply.

I dont have any null for 3,4 division.bec there is no 3,4 th division row.

My data will be
CA 1
CA 2

3,4 no record in my table. if use that

DECLARE @State VARCHAR(50)
SET @State ='CA'
Declare @OutputString varchar(max)
SELECT @OutputString = coalesce(@OutputString + ',','') + case when Division is not null then '1' else '0' end
FROM
(

Select Distinct ShortState + ',' + Division as Division from table1 where ShortState=@State

) tbl

PRINT @OutputString

i am retrieving 1,1

But i want 1,1,0,0

can u please help me for this?

Visa.G

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-26 : 09:58:08

I added a second query if that was the case - must have missed the deadline
And here it is again but without the mistake (or at least one of them)

DECLARE @State VARCHAR(50)
SET @State ='CA'
Declare @OutputString varchar(max)
SELECT @OutputString = coalesce(@OutputString + ',','') + case when Division is not null then '1' else '0' end
FROM
(
select
(Select i = 1 union all select 2 union all select 3 union all select 4) a
left join
Table1 t
on t.division = a.i
and ShortState=@State
) tbl

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visa123
Yak Posting Veteran

54 Posts

Posted - 2010-11-26 : 10:19:58

Hi

Thanks for your reply.

i had one more problem

CA 1
CA 1
CA 2

Sometimes it will 1 (1,2,3,4 ) in double (or more) times.So for i put distinct in above.But if i use this it thorws wrong result.

if there is no duplicate division its working fine.

Can you please help for this ?

Visa.G

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-26 : 10:28:07

DECLARE @State VARCHAR(50)
SET @State ='CA'
Declare @OutputString varchar(max)
SELECT @OutputString = coalesce(@OutputString + ',','') + case when Division is not null then '1' else '0' end
FROM
(
select
(Select i = 1 union all select 2 union all select 3 union all select 4) a
left join
(select distinct division from Table1 where ShortState=@State) t
on t.division = a.i
) tbl

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visa123
Yak Posting Veteran

54 Posts

Posted - 2010-11-26 : 10:49:35

Thank you very much.Its working fine.

Visa.G

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources