Matrix calculations using SQL Server table

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)

Matrix calculations using SQL Server table

Author

Topic

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2011-09-26 : 03:30:43

Table1:
[Column1] [Column2] [Column3]
AAA FFF JJJ
BBB GGG KKK
CCC HHH
III

The above table as 3 columns and some rows respectively. Need the result from above table as

[C1][C2][C3]
AAA FFF JJJ
AAA FFF KKK
AAA GGG JJJ
AAA GGG KKK
AAA HHH JJJ
AAA HHH KKK
AAA III JJJ
AAA III KKK
BBB FFF JJJ
BBB FFF KKK
BBB GGG JJJ
BBB GGG KKK
BBB HHH JJJ
BBB HHH KKK
BBB III JJJ
BBB III KKK
CCC FFF JJJ
CCC FFF KKK
CCC GGG JJJ
CCC GGG KKK
CCC HHH JJJ
CCC HHH KKK
CCC III JJJ
CCC III KKK

Its like Attretion Matrix method. Column1 as 3 rows, Column2 as 4 rows and Column3 as 2 rows. 3*4*2=24 rows will be the result.
Please help me to get the solution for this...

Regards,
Kalaiselvan R
Love Yourself First....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-26 : 04:06:43

[code]SELECT a.Column1,b.Column2,c.Column3
FROM (SELECT DISTINCT Column1 FROM Table1) a
CROSS JOIN (SELECT DISTINCT Column2 FROM Table1) b
CROSS JOIN (SELECT DISTINCT Column3 FROM Table1) c
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2011-09-26 : 08:34:17

Thanks its working fine.
Like the same If the Columns count were Dynamic, how we can use the same process to get the result.

For Ex; In above Table1 we have 3 columns. If we doesnt know the Column name and its count.
Ex: Table1 --> (Below Example)
Table 2 --> [Column1] [Column2] [Column2] [Column4]
AA FF KK RR
GG LL SS
HH TT

Table2 will have 1*3*2*3=18 rows of values. Like the same Many tables can created and we need to Built a Query for this Attretion.
Please help me in this to get the Solution too.

Regards,
Kalaiselvan R
Love Yourself First....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-26 : 09:21:04

sorry but i cant see the need of all this. what the purpose of this requirment? why your columns are dynamic?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2011-09-27 : 11:24:39

Ita a CRM project. Here the table will be created dynamically.

Regards,
Kalaiselvan R
Love Yourself First....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-27 : 12:22:21

then you've to use dynamic sql. for getting column names dynamically make use of INFORMATION_SCHEMA.COLUMNS catalog view

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources