Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

arrange data for a report Expand / Collapse
Author
Message
Posted Friday, June 21, 2013 10:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 7:19 AM
Points: 25, Visits: 177
Hello, I seem to be having a problem arranging my data in a way that can be utilized by a specific report

To simplify, after all the processing I end up with a table of column pairs (should not be more than a hundred records at most) that will look like so: (SQL Server 2005)


CREATE TABLE Links(
LinkID INT NOT NULL IDENTITY(1,1)
CONSTRAINT PK_Links_linkID PRIMARY KEY (LinkID),
Col1 INT NOT NULL,
Col2 INT NOT NULL
)

INSERT INTO Links (Col1, Col2)
SELECT 1,2
UNION ALL
SELECT 1,3
UNION ALL
SELECT 2,4
UNION ALL
SELECT 5, 3
UNION ALL
SELECT 3,2
UNION ALL
SELECT 7, 8
UNION ALL
SELECT 6,7
UNION ALL
SELECT 4, 1
UNION ALL
SELECT 1, 4



I need to write a select that will rearrange
the values in this table so that all the relationships between the 2 columns are presented
in a way that col1 contains the minimum value of the relationship and all others belong to this minimum value

Like so:

Col1 Col2
1 2
1 3
1 4
1 5
6 7
6 8

I was trying to use the recursive CTEs but did not succeed - I am hopeful there is a decent way of doing this - I would hate to use any kind of "row by row" approach

Thank you
Post #1466278
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse