How can I 'unfold' a count to 1, 2, 3,..., n records?

Hi all,
I have a simple query that retrieves the number of articles that an order
has:
SELECT ArticleId, ArticleCount FROM TOrderDetails WHERE OrderId = @.MyOrder
Both ArticleId and ArticleCount are integers. A sample set result woud be
like this one:
ArticleId ArticleCount
1 3
2 2
3 5
4 1
I need to print the labels for each single article that is sold. I need to
expand/unfold that result set into something like:
ArticleId ArticleCountId
1 1
1 2
1 3
2 1
2 2
3 1
3 2
3 3
3 4
3 5
4 1
So, there will be 3 records for ArticleId = 1: Since 3 units are sold, I
will need 3 labels (named 1, 2 and 3), and so on.
I've been thinking about how to acomplish this but the only thing that goes
to my mind is passing a table to a sp to iterate for i=1 to n for each Id.
However, since passing a table to a SP is not possible I'm a little
stuck-in-the-mud :(
PD: I'm using Reporting Services so it is a little hard to do job with a
simple FOR statement since RS is not very good for being programmed the way
we were used with VB. Of course I could do an assembly and do the
programming there but I would rather do the job in the query/sql server and
let RS as neat/simple as possible.
Any ideas? Regards and thanks in advance.Hi
If you have a number table or derived table of numbers, then something like
the following would work:
SELECT A.ArticleId, C.Num
FROM ( SELECT 1 As Num
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5 ) C
JOIN (SELECT 1 AS ArticleId, 3 AS ArticleCount
UNION ALL SELECT 2, 2
UNION ALL SELECT 3, 5
UNION ALL SELECT 4, 1 ) A ON C.Num <= A.ArticleCount
ORDER BY A.ArticleId, C.Num
John
"David Lightman Robles" wrote:

> Hi all,
> I have a simple query that retrieves the number of articles that an order
> has:
> SELECT ArticleId, ArticleCount FROM TOrderDetails WHERE OrderId = @.MyOrd
er
> Both ArticleId and ArticleCount are integers. A sample set result woud be
> like this one:
> ArticleId ArticleCount
> 1 3
> 2 2
> 3 5
> 4 1
> I need to print the labels for each single article that is sold. I need to
> expand/unfold that result set into something like:
> ArticleId ArticleCountId
> 1 1
> 1 2
> 1 3
> 2 1
> 2 2
> 3 1
> 3 2
> 3 3
> 3 4
> 3 5
> 4 1
> So, there will be 3 records for ArticleId = 1: Since 3 units are sold, I
> will need 3 labels (named 1, 2 and 3), and so on.
> I've been thinking about how to acomplish this but the only thing that goe
s
> to my mind is passing a table to a sp to iterate for i=1 to n for each Id.
> However, since passing a table to a SP is not possible I'm a little
> stuck-in-the-mud :(
>
> PD: I'm using Reporting Services so it is a little hard to do job with a
> simple FOR statement since RS is not very good for being programmed the wa
y
> we were used with VB. Of course I could do an assembly and do the
> programming there but I would rather do the job in the query/sql server an
d
> let RS as neat/simple as possible.
> Any ideas? Regards and thanks in advance.
>
>|||David
SELECT IDENTITY(INT) "n" INTO Numbers
FROM sysobjects s1
CROSS JOIN sysobjects s2
GO
CREATE TABLE #Test
(
col1 INT,
col2 INT
)
INSERT INTO #Test VALUES (1,3)
INSERT INTO #Test VALUES (2,2)
SELECT col1,n FROM #Test LEFT JOIN Numbers
ON #Test.col2 >= Numbers.n
"David Lightman Robles" <dlightman@.NOSPAMiname.com> wrote in message
news:%23vwfl43WFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I have a simple query that retrieves the number of articles that an order
> has:
> SELECT ArticleId, ArticleCount FROM TOrderDetails WHERE OrderId =
@.MyOrder
> Both ArticleId and ArticleCount are integers. A sample set result woud be
> like this one:
> ArticleId ArticleCount
> 1 3
> 2 2
> 3 5
> 4 1
> I need to print the labels for each single article that is sold. I need to
> expand/unfold that result set into something like:
> ArticleId ArticleCountId
> 1 1
> 1 2
> 1 3
> 2 1
> 2 2
> 3 1
> 3 2
> 3 3
> 3 4
> 3 5
> 4 1
> So, there will be 3 records for ArticleId = 1: Since 3 units are sold, I
> will need 3 labels (named 1, 2 and 3), and so on.
> I've been thinking about how to acomplish this but the only thing that
goes
> to my mind is passing a table to a sp to iterate for i=1 to n for each Id.
> However, since passing a table to a SP is not possible I'm a little
> stuck-in-the-mud :(
>
> PD: I'm using Reporting Services so it is a little hard to do job with a
> simple FOR statement since RS is not very good for being programmed the
way
> we were used with VB. Of course I could do an assembly and do the
> programming there but I would rather do the job in the query/sql server
and
> let RS as neat/simple as possible.
> Any ideas? Regards and thanks in advance.
>|||You can create a digits table and cross join it.
e.g.
select top 8000 digit=identity(int,1,1)
into digits
from sysobjects,syscolumns
SELECT ArticleId, ArticleCount=digit
FROM TOrderDetails cross join digits
WHERE OrderId = @.MyOrder
and ArticleCount>=digit
ORDER BY ArticleId
-oj
"David Lightman Robles" <dlightman@.NOSPAMiname.com> wrote in message
news:%23vwfl43WFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I have a simple query that retrieves the number of articles that an order
> has:
> SELECT ArticleId, ArticleCount FROM TOrderDetails WHERE OrderId =
> @.MyOrder
> Both ArticleId and ArticleCount are integers. A sample set result woud be
> like this one:
> ArticleId ArticleCount
> 1 3
> 2 2
> 3 5
> 4 1
> I need to print the labels for each single article that is sold. I need to
> expand/unfold that result set into something like:
> ArticleId ArticleCountId
> 1 1
> 1 2
> 1 3
> 2 1
> 2 2
> 3 1
> 3 2
> 3 3
> 3 4
> 3 5
> 4 1
> So, there will be 3 records for ArticleId = 1: Since 3 units are sold, I
> will need 3 labels (named 1, 2 and 3), and so on.
> I've been thinking about how to acomplish this but the only thing that
> goes to my mind is passing a table to a sp to iterate for i=1 to n for
> each Id. However, since passing a table to a SP is not possible I'm a
> little stuck-in-the-mud :(
>
> PD: I'm using Reporting Services so it is a little hard to do job with a
> simple FOR statement since RS is not very good for being programmed the
> way we were used with VB. Of course I could do an assembly and do the
> programming there but I would rather do the job in the query/sql server
> and let RS as neat/simple as possible.
> Any ideas? Regards and thanks in advance.
>|||Instead of counting try ranking:
DROP TABLE #article_sales
CREATE TABLE #article_sales ( as_id INT IDENTITY, article_id TINYINT,
sale_date DATETIME )
INSERT INTO #article_sales SELECT 1, '1 Apr 2005'
INSERT INTO #article_sales SELECT 1, '2 Apr 2005'
INSERT INTO #article_sales SELECT 1, '3 Apr 2005'
INSERT INTO #article_sales SELECT 2, '1 Apr 2005'
INSERT INTO #article_sales SELECT 2, '2 Apr 2005'
INSERT INTO #article_sales SELECT 3, '1 Apr 2005'
INSERT INTO #article_sales SELECT 3, '2 Apr 2005'
INSERT INTO #article_sales SELECT 3, '3 Apr 2005'
INSERT INTO #article_sales SELECT 3, '4 Apr 2005'
INSERT INTO #article_sales SELECT 3, '5 Apr 2005'
INSERT INTO #article_sales SELECT 4, '1 Apr 2005'
-- Your count query
SELECT article_id, COUNT(*)
FROM #article_sales
GROUP BY article_id
-- Instead of counting, try ranking
SELECT
as1.article_id,
( SELECT COUNT(*) FROM #article_sales as2 WHERE as1.article_id =
as2.article_id AND as2.as_id < as1.as_id ) + 1 rank
FROM #article_sales as1
Let me know how you get on.
Damien
"David Lightman Robles" wrote:

> Hi all,
> I have a simple query that retrieves the number of articles that an order
> has:
> SELECT ArticleId, ArticleCount FROM TOrderDetails WHERE OrderId = @.MyOrd
er
> Both ArticleId and ArticleCount are integers. A sample set result woud be
> like this one:
> ArticleId ArticleCount
> 1 3
> 2 2
> 3 5
> 4 1
> I need to print the labels for each single article that is sold. I need to
> expand/unfold that result set into something like:
> ArticleId ArticleCountId
> 1 1
> 1 2
> 1 3
> 2 1
> 2 2
> 3 1
> 3 2
> 3 3
> 3 4
> 3 5
> 4 1
> So, there will be 3 records for ArticleId = 1: Since 3 units are sold, I
> will need 3 labels (named 1, 2 and 3), and so on.
> I've been thinking about how to acomplish this but the only thing that goe
s
> to my mind is passing a table to a sp to iterate for i=1 to n for each Id.
> However, since passing a table to a SP is not possible I'm a little
> stuck-in-the-mud :(
>
> PD: I'm using Reporting Services so it is a little hard to do job with a
> simple FOR statement since RS is not very good for being programmed the wa
y
> we were used with VB. Of course I could do an assembly and do the
> programming there but I would rather do the job in the query/sql server an
d
> let RS as neat/simple as possible.
> Any ideas? Regards and thanks in advance.
>
>|||Thansk Uri & Oj for your hint about using an auxiliary table with numbers
and a cross join. It really did the job much easier than I thought it would
be.
Regards.
"David Lightman Robles" <dlightman@.NOSPAMiname.com> escribi en el mensaje
news:%23vwfl43WFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I have a simple query that retrieves the number of articles that an order
> has:
> SELECT ArticleId, ArticleCount FROM TOrderDetails WHERE OrderId =
> @.MyOrder
> Both ArticleId and ArticleCount are integers. A sample set result woud be
> like this one:
> ArticleId ArticleCount
> 1 3
> 2 2
> 3 5
> 4 1
> I need to print the labels for each single article that is sold. I need to
> expand/unfold that result set into something like:
> ArticleId ArticleCountId
> 1 1
> 1 2
> 1 3
> 2 1
> 2 2
> 3 1
> 3 2
> 3 3
> 3 4
> 3 5
> 4 1
> So, there will be 3 records for ArticleId = 1: Since 3 units are sold, I
> will need 3 labels (named 1, 2 and 3), and so on.
> I've been thinking about how to acomplish this but the only thing that
> goes to my mind is passing a table to a sp to iterate for i=1 to n for
> each Id. However, since passing a table to a SP is not possible I'm a
> little stuck-in-the-mud :(
>
> PD: I'm using Reporting Services so it is a little hard to do job with a
> simple FOR statement since RS is not very good for being programmed the
> way we were used with VB. Of course I could do an assembly and do the
> programming there but I would rather do the job in the query/sql server
> and let RS as neat/simple as possible.
> Any ideas? Regards and thanks in advance.
>