arrange data for a report

• slatinsky

  SSC Journeyman

  Points: 86

  More actions

  June 21, 2013 at 10:00 am

  #150428

  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:

  Col1Col2

  12

  13

  14

  15

  67

  68

  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