Help on the 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

SQL Server 2005 Forums

Transact-SQL (2005)

Help on the query

Author

Topic

Sarakumar
Posting Yak Master

108 Posts

Posted - 2009-05-11 : 23:36:01

declare @Customer table
(cid int,
item varchar(50),
in_use bit)
insert into @Customer values (1,'mobile',0)
insert into @Customer values (1,'TV',1)
insert into @Customer values(1,'Comp',1)
insert into @Customer values(1,'Laptop',0)
insert into @Customer values(2,'mobile',1)
insert into @Customer values(2,'TV',1)
insert into @Customer values(2,'Comp',1)
insert into @Customer values(2,'Laptop',1)
insert into @Customer values(3,'mobile',0)
insert into @Customer values(3,'TV',0)
insert into @Customer values(3,'Comp',0)
insert into @Customer values(3,'Laptop',0)

SELECT cid, [mobile],[TV], [Comp],[Laptop]from
(select cid, item ,in_use from @Customer where in_use =0 or in_use =1) st
pivot
(count(item) for item in([mobile],[TV], [Comp],[Laptop]))PT
order by cid

How do i get the following result in the above

CID Mobile TV Comp Laptop
1 NO YES YES NO
2 YES YES YES YES
3 NO NO NO No

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-11 : 23:37:01

didn't we have settled this yesterday ?

KH
[spoiler]Time is always against us[/spoiler]

Sarakumar
Posting Yak Master

108 Posts

Posted - 2009-05-11 : 23:40:21

yesterday i just used wehre in_use = 1 ..
but in my scenrio, i can have eithere in_use =1 or in_use=0 ..
if i try the above query i gives wrong output, pls help

Sarakumar
Posting Yak Master

108 Posts

Posted - 2009-05-11 : 23:50:41

Yes.it affects my required result..
CID Mobile TV Comp Laptop
1 NO YES YES NO
2 YES YES YES YES
3 NO NO NO No
B'coz base on the Yes no value only i can display on the Datagrid, if the Item is in_use or not.
Help pls

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-11 : 23:53:12

[code]
SELECT cid, [mobile], [TV], [Comp], [Laptop]
from
(
select cid, item, in_use = convert(int, in_use)
from @Customer
) st
pivot
(
sum(in_use)
for item in ( [mobile], [TV], [Comp], [Laptop] )
) PT
order by cid
[/code]

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-11 : 23:54:33


SELECT 	cid, [mobile], [TV], [Comp], [Laptop]
from
(
	select 	cid, item, in_use = nullif(in_use, 0)
	from 	@Customer
) st
pivot
(
	count(in_use)
	for item in ( [mobile], [TV], [Comp], [Laptop] ) 
) PT
order by cid

KH
[spoiler]Time is always against us[/spoiler]

Sarakumar
Posting Yak Master

108 Posts

Posted - 2009-05-12 : 04:14:06

Thanks..it works

Sarakumar
Posting Yak Master

108 Posts

Posted - 2009-05-15 : 05:01:25

Hai, Sorroy for restarting the same thread. ur query works fine, if i have only one table..but suddenly we changed the design.
Now we have diffrent design,
Customer inforamation is splitted into one table
and item information is in diffrent tabl..

declare @Customer table
(cid int,
cname varchar(50),
cust_Sts bit )
insert into @Customer values (1,'One',1)
insert into @Customer values (2,'Two',1)
insert into @Customer values(3,'Three',1)

declare @TblCode table
(cid int,
item varchar(20),
in_use bit)

insert into @TblCode values (1,'Laptop',1)
insert into @TblCode values (1,'Mobile',0)
insert into @TblCode values(1,'TV',0)
insert into @TblCode values(1,'Keyboard',1)
insert into @TblCode values (2,'Laptop',0)
insert into @TblCode values (2,'Mobile',0)
insert into @TblCode values(2,'TV',0)
insert into @TblCode values(2,'Keyboard',0)
insert into @TblCode values (3,'Laptop',1)
insert into @TblCode values (3,'Mobile',1)
insert into @TblCode values(3,'TV',1)
insert into @TblCode values(3,'Keyboard',1)

SELECT cid, [mobile], [TV], [Comp], [Laptop]
from
(
select a.cid, item,cust_sts
from @Customer a
inner join @TblCode b on a.cid =b.cid
where 1=1 and (in_use = 0 or in_use =1)
) st
pivot
(
count(item)
for item in ( [mobile], [TV], [Comp], [Laptop] )
) PT
order by cid

i want the folloing result

cid name sts [mobile], [TV], [Comp], [Laptop]
1 One Active 0 0 1 1
2 Two Active 0 0 0 0
3 THree Active 1 1 1 1

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-15 : 05:32:48

just follow the technique i used in post 05/11/2009 : 23:53:12 & 05/11/2009 : 23:54:33 and you will get what you want.

by the way . . your sample data and the result are not consistent. You have keyboard in the sample data but not in your query and the expected result

KH
[spoiler]Time is always against us[/spoiler]

Sarakumar
Posting Yak Master

108 Posts

Posted - 2009-05-15 : 05:40:50

Hai i tried ur techinque and framed the follwoing query ,
but it is giving error
Msg 207, Level 16, State 1, Line 61
Invalid column name 'in_use'.
Msg 207, Level 16, State 1, Line 62
Invalid column name 'item'.

SELECT cid, [mobile], [TV], [Comp], [Laptop]
from
(
select a.cid, cname, cust_Sts --,in_use = convert(int, in_use)
from @Customer a
inner join @TblCode b on a.cid =b.cid
) st
pivot
(
count(in_use)
for item in ( [mobile], [TV], [Comp], [Laptop] )
) PT
order by cid

i think im confusing too much..sorry

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-15 : 05:51:02

you are mixing up the technique in the 2 query i posted.


SELECT cid, cname, cust_sts, [Mobile], [TV], [Comp], [Laptop]
from
(
	select a.cid, cname, item, cust_sts, in_use = nullif(in_use, 0)
	from 	@Customer a
		inner join @TblCode b on a.cid = b.cid
) st
pivot
(
	count(in_use)
	for item in ( [Mobile], [TV], [Comp], [Laptop] )
) PT
order by cid

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources