Related Data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Related Data

Author

Topic

benking9987
Posting Yak Master

124 Posts

Posted - 2011-11-11 : 14:00:51

I have a table with item data. My columns are as follows:
Style, Color, Gender, TotalSales

What I would like to end up with is a complete record set of how the data all relates to eachother. So, if a given style shares a color, and gender with other styles, then those styles are said to be related. For example if Style1 is black and meant for women, and Style5 is also black and meant for women, then those styles are related styles.
The record set I would like will be a fully extrapolated record set showing EACH Style in the table and all of the styles that it is associated with. So if Style1, Style13, and Style16 are all related, then the recordset would look like this:

Style1, Style13
Style1, Style16
Style13, Style1
Style13, Style16
Style16, Style1
Style16, Style13

I don’t know what is the best way to end up at the resulting data. Not sure if I can do it with some sort of complex SELECT statement or if I’ll need some T-SQL procedure. Here is what I have so far:

SELECT Style, Color, Gender, TotalSales ROW_NUMBER() OVER ( PARTITION BY  [Color], [Gender] ORDER BY [TotalSales] DESC ) AS Rank
FROM RelatedData

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2011-11-11 : 20:17:05

Try this:


SELECT DISTINCT T1.Style, T2.Style
  FROM TableName AS T1
       INNER JOIN
       TableName AS T2
       ON T2.Style <> T1.Style
          AND T2.Color = T1.Color
          AND T2.Gender = T1.Gender

For us, there is only the trying. The rest is not our business. ~T.S. Eliot

Muhammad Al Pasha

benking9987
Posting Yak Master

124 Posts

Posted - 2011-11-14 : 17:54:30

The logic of your statement worked perfectly. I just needed to massage it a little bit to get it to return the exact values I needed.

Thank you for your post!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources