question

technette avatar image
technette asked

Select unique reference number and associated data

Hi, I have created a table that contains base part number and all of the sub components. The base part number is repeated for each row and I need to present data according to the requirement. One base part than all related data. How can I do this with a select statement? Example Table BasePart Data Data Data Data BasePart Data Data Data Data BasePart Data Data Data Data BasePart Data Data Data Data BasePart Data Data Data Data Need to show: BasePart Data Data Data Data Data Data Data Data Data Data Data Data Data Data Data Data Data Data Data Data
selectunique
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Dave_Green avatar image
Dave_Green answered
Well, first of all I'd note that SQL isn't really designed for this - ideally use a presentation layer (SSRS?) to do this. However it is possible. Making some assumptions about keys: DECLARE @t TABLE ( col1 VARCHAR(10) , col2 VARCHAR(10) , col3 VARCHAR(10) , col4 VARCHAR(10) , col5 VARCHAR(10) ) INSERT @t (col1,col2,col3,col4,col5) VALUES ('Basepart','Data','Data','Data','Data') INSERT @t (col1,col2,col3,col4,col5) VALUES ('Basepart','Data','Data','Data','Data') INSERT @t (col1,col2,col3,col4,col5) VALUES ('Basepart','Data','Data','Data','Data') INSERT @t (col1,col2,col3,col4,col5) VALUES ('Basepart','Data','Data','Data','Data') INSERT @t (col1,col2,col3,col4,col5) VALUES ('Basepart','Data','Data','Data','Data') ; WITH ordered ( col1, col2, col3, col4, col5, rn ) AS ( SELECT * , ROW_NUMBER() OVER ( ORDER BY col1, col2 ) FROM @t ) SELECT CASE WHEN b.rn IS NULL THEN a.col1 ELSE '' END AS col1 , a.col2 , a.col3 , a.col4 , a.col5 FROM ordered a LEFT JOIN ordered b ON a.rn = b.rn + 1 I've added a row number to the result set, joined it to the next record in the set, and used that to determine if we should print the entry in the first col. You'll have to adjust for your keys, etc., but this should get you on the right track.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

You will also need to adjust the order by clause of the ROW_NUMBER to suit your data, and probably add an order by to the end select statement.
0 Likes 0 ·
Dave, Thank you for responding. This is really good. My data may have between 3 and 80 rows for each BasePart. It may be troublesome, but it looks like I may have to use your suggestion to do this in SSRS. Just hope I can do this quickly.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.