November 23, 2003 at 11:26 am
I am trying to create a procedure that outputs a list of csv strings where each one represents a possible combination of IDs between each group in the table. Also, the number of members in each string is based on the number of groupings in the table.
We are not allowed to use dynamic sql in our shop and I can't see any other way to get it done.
TABLE 1
ID|GROUP
1|1
2|1
3|2
4|2
5|2
output
1|3
1|4
1|5
2|3
2|4
2|5
or
TABLE 1
ID|GROUP
1|1
2|1
3|2
4|2
5|3
6|3
output
1,3,5
1,3,6
1,4,5
1,4,6
2,3,5
2,3,6
2,4,5
2,4,6
November 23, 2003 at 12:00 pm
After reading my own post I thought I would narrow the scope of my problem. To get the previous examples I could run:
select a.ID, b.ID
from TABLE1 a cross join TABLE1 b
where a.GROUP=1 and b.GROUP=2
select a.ID, b.ID, c.ID
from TABLE1 a cross join TABLE1 b cross join TABLE1 c
where a.GROUP=1 and b.GROUP=2 and c.GROUP=3
I need an elegant way to accomplish the same thing with having to build these statements as strings first. It seems there must be a way do this recursively.
November 24, 2003 at 12:57 am
Are you allowed to use temporary tables ?
November 24, 2003 at 1:02 am
Yes. That would be fine. Thanks.
November 24, 2003 at 3:40 am
Try this for start. It uses two temporary tables and one cursor, but it does not use recursion.
CREATE PROCEDURE MAKE_CSV
AS
SET NOCOUNT ON
DECLARE @group int
CREATE TABLE #tmp_csv1 (csv_value varchar(1000))
CREATE TABLE #tmp_csv2 (csv_value varchar(1000))
DECLARE csrGroups CURSOR LOCAL FAST_FORWARD FOR
SELECT DISTINCT [GROUP] FROM TABLE1 ORDER BY [GROUP]
OPEN csrGroups
FETCH NEXT FROM csrGroups INTO @group
IF @@FETCH_STATUS = 0
BEGIN
INSERT INTO #tmp_csv1 (csv_value)
SELECT CONVERT(varchar, [ID]) FROM TABLE1
WHERE [GROUP] = @group
FETCH NEXT FROM csrGroups INTO @group
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #tmp_csv2 (csv_value) SELECT csv_value FROM #tmp_csv1
DELETE FROM #tmp_csv1
INSERT INTO #tmp_csv1 (csv_value)
SELECT csv_value + ',' + CONVERT(varchar, [ID])
FROM #tmp_csv2 CROSS JOIN TABLE1
WHERE (TABLE1.[GROUP] = @group)
DELETE FROM #tmp_csv2
FETCH NEXT FROM csrGroups INTO @group
END
END
CLOSE csrGroups
DEALLOCATE csrGroups
SELECT csv_value FROM #tmp_csv1 ORDER BY csv_value
GO
November 24, 2003 at 4:41 am
Just came across this one. It might help
http://vyaskn.tripod.com/hierarchies_in_sql_server_databases.htm
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
November 24, 2003 at 6:45 am
If table is as described and GROUP is sequential (no gaps) then you can use two temp tables as follows
DECLARE @group int,@max int
SET @group = 1
SELECT @max = MAX([GROUP]) FROM tablea
CREATE TABLE #temp (result varchar(100))
CREATE TABLE #temp2 (result varchar(100))
SET NOCOUNT ON
INSERT INTO #temp SELECT cast([ID] as varchar) FROM tablea WHERE [GROUP] = @group
SET @group = @group + 1
WHILE (@group <= @max)
BEGIN
TRUNCATE TABLE #temp2
INSERT INTO #temp2
SELECT t.result + '|' + cast(a.[ID] as varchar)
FROM #temp t
CROSS JOIN tablea a
WHERE a.[GROUP] = @group
TRUNCATE TABLE #temp
INSERT INTO #temp SELECT result from #temp2
SET @group = @group + 1
END
SET NOCOUNT OFF
SELECT * FROM #temp ORDER BY result
DROP TABLE #temp
DROP TABLE #temp2
Far away is close at hand in the images of elsewhere.
Anon.
November 24, 2003 at 10:37 am
Thanks to all who responded. I was able to adapt the techniques expressed (or referred to) here to my particular situation.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply