SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Hierarchy SQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

demons
Starting Member

Vietnam
6 Posts

Posted - 10/24/2013 :  22:01:22  Show Profile  Reply with Quote
Dear all
I have SQL as below

CREATE TABLE #PP (Pid int, QtyOut int)
INSERT INTO #PP(Pid, QtyOut) VALUES (1, 2)
INSERT INTO #PP(Pid, QtyOut) VALUES (2, 3)

DROP TABLE #PP

I expect the result as
Pid Qty
1 1
1 1
2 1
2 1
2 1
I don't want to use WHILE and INSERT

Please help me to solve
Thanks all

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 10/25/2013 :  00:29:48  Show Profile  Reply with Quote
;WITH Cte(Pid, QtyOut, num) AS
(SELECT Pid, QtyOut, 1 FROM #PP
 UNION ALL
 SELECT Pid, QtyOut, num+1 FROM Cte WHERE num<QtyOut)
SELECT Pid, QtyOut, QtyOut/COUNT(*) OVER(PARTITION BY Pid) Qty 
FROM Cte

ORDER BY Pid

NOTE: you can simply put 1 instead of QtyOut/COUNT(*) OVER(PARTITION BY Pid) Qty

--
Chandu
Go to Top of Page

demons
Starting Member

Vietnam
6 Posts

Posted - 10/25/2013 :  21:17:01  Show Profile  Reply with Quote
Thanks
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 11/13/2013 :  05:14:41  Show Profile  Reply with Quote
Welcome

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000