Cross linking of 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 2008 Forums

Transact-SQL (2008)

Cross linking of data

Author

Topic

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2014-12-04 : 10:22:23

Hi Everyone,

I need to cross link the columns as per below requirement -

I have a table

CREATE TABLE #temp
(
F1 VARCHAR(3),
F2 VARCHAR(3),
F3 VARCHAR(3)
)

INSERT INTO #temp
SELECT 'AAA', '111', '12' UNION ALL
SELECT 'AAA', '111', '13' UNION ALL
SELECT 'AAA', '222', '14' UNION ALL
SELECT 'AAA', '222', '15' UNION ALL
SELECT 'BBB', '333', '16' UNION ALL
SELECT 'BBB', '333', '17'

I need to get all possible combination of F2 and F3 columns group by F1 column so expected result set should be -

AAA 111 12
AAA 111 13
AAA 222 14
AAA 222 15
AAA 111 14
AAA 111 15
AAA 222 12
AAA 222 13
BBB 333 16
BBB 333 17

Thanks in advance.

Vaibhav T

If I cant go back, I want to go fast...

marek_gd
Starting Member

6 Posts

Posted - 2014-12-04 : 10:35:18

Hi,

try this:

select
distinct a.f1, b.f2, c.f3
from (select f1 from #temp) as a
cross join (select f2 from #temp) as b
cross join (select f3 from #temp) as c
order by a.f1, b.f2, c.f3

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2014-12-05 : 06:45:41

Thanks for the reply but this will cross join all the rows but I want cross join of F2 and F2 group by F1. That means it should not cross link between one f1 value with other f1 value.

Vaibhav T

If I cant go back, I want to go fast...

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-05 : 08:57:54

This otta do it:


SELECT DISTINCT a.f1
              , c.f2
              , c.f3
  FROM #temp AS a
       CROSS APPLY(
    SELECT b.f2
         , c.f3
      FROM #temp b
           CROSS JOIN #temp c
      WHERE a.f1 = b.f1 AND a.f1 = c.f1)c
  ORDER BY a.f1, c.f2, c.f3;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources