Hierarchy SQL - 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 2008 Forums

Transact-SQL (2008)

Hierarchy SQL

Author

Topic

demons
Starting Member

6 Posts

Posted - 2013-10-24 : 22:01:22

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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-10-25 : 00:29:48

[code];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 [/code]
ORDER BY Pid

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

--
Chandu

demons
Starting Member

6 Posts

Posted - 2013-10-25 : 21:17:01

Thanks

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-11-13 : 05:14:41

Welcome

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources