Database

declare @tbl table(id int identity, refid int)
insert into @tbl 
	select 0 union all select 1 union all
	select 2 union all select 1 union all
	select 1

select t.id,t.refid,case when x.cnt is null then 0 else x.cnt end as total
from @tbl t
left join
(
select refid,count(refid) as cnt
from @tbl
where refid <> 0
group by refid
having (count(refid)>0)) x
on t.id = x.refid

1	0	3
2	1	1
3	2	0
4	1	0
5	1	0

sarang_k wrote:

you wil get another window then click on add and choose your database from the pen drive.

Case <expression>
when 0 then <expression>
when 1 then <expression>
ELSE <expression> 
END

Mycroft Holmes wrote:

Send me the codz.

sarang_k wrote:

How to use the Case in stored procedure

is a normal thing.

Create procedure TestCaseStatement
as

Begin
	declare @tbl table(id int)
	insert into @tbl 
	select 0 union all select 1 union all
	select 2 union all select 1 union all
	select 1
		
	select 
		id
		,case when id = 1 then 'One' else 'Other Number' end as Value	
	 from @tbl
End

Create procedure TestCaseStatement
as

Begin
	declare @tbl table(id int)
	insert into @tbl 
	select 0 union all select 1 union all
	select 2 union all select 1 union all
	select 1
		
	select 
		id
		,Value = case when id = 1 then 'One' else 'Other Number' end 
	 from @tbl
End

0	Other Number
1	One
2	Other Number
1	One
1	One

select *
From Widgets
Where CustomerID = @CustID

select count(*),WidgetTypeID
From Widgets
Where CustomerID = @CustID
GroupBy WidgetTypeID

Insert OtherTable(CustID,WidgetTypeID, Qty)
select CustID, WidgetTypeID, count(*)
From Widgets
Where CustomerID = @CustID
GroupBy CustID, WidgetTypeID

declare @tblCustomers table(CustID int,CustomerName varchar(50))
declare @tblWidgets table(CustID int,WidgetTypeID int, Qty int)
declare @tblTarget table(CustID int,WidgetTypeID int, Qty int)
insert into @tblCustomers values(101,'Cust1'),(102,'Cust2'),(103,'Cust3')
insert into @tblWidgets values(101,1,5),(101,2,1),(101,3,3),(102,10,5),(103,4,1)

insert into @tblTarget
select CustID,WidgetTypeID,Qty
from
(
	select w.CustID,w.WidgetTypeID,w.Qty from @tblWidgets w
	inner join @tblCustomers c
	on c.CustID = w.CustID
)X where X.CustID =101
select * from @tblTarget

CustID	WidgetTypeID	Qty
101	1	5
101	2	1
101	3	3

ffowler wrote:

So for example customer A brought 3 widget items: Widget1 5 pieces, Widget2 1 piece, and Widget3 3 pieces.

What I need to end up with is a new table with 9 entries. 1 for each widget piece.

CustId  WidgetID
A       W1
A       W1
A       W1
A       W1
A       W1
A       W2
A       W3
A       W3
A       W3