Help Required on the SQL Script...

Jay_se

I am having the below secnario. Table 1 Col1 Col2 Col3 10 20 30 Table 2 Code Name 1 Col1 2 Col2 3 Col3 Expected Output: 1 Col1 10 2 Col2 20 3 Col3 30 How to write the SQL script (SQL server 2005) for this? Thanks in advance.

Jey

David Mujica

Are you sure about the values in Table 1 ? From your description, there is one row with three columns where col1 contains a value of 10, col2 contains a value of 20 and col3 contains a value of 30. This is not a typical relational database method for storing data. You may want to re-check your homework assignment. BTW: Most people won't help you with your homework.

Blue_Boy

Your table data and structure doesn't seems to me good enought logical. What if Table1 have different values? What if Table1 contain these values :

Col1 Col2 Col3
10 20 30
100 200 300
1000 2000 3000
10000 20000 30000

and so on.

I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post.

Jay_se

Hi, Thanks for your reply. I do aware of it. But my requirement is similar like this. I needs to get all the column names from the Table 1 & lookup on Table 2 and get the Code of that respective Column name. Let me explain: In table 1 there is a column called 'LoadFactor' & its value is 80 LoadFactor 80 Table 2 Code Name 1 LoadFactor Table 3 Code LSL USL 1 50 100 My Required Output: Code Name 'Actual Value' LSL USL 1 LoadFactor 80 50 100 Now. have u got it? This is a simple example that explains my requirement. I do have 500 similar columns with few input tables.

Jey

Jay_se

Hi, Thanks for your valuable reply. Please assume that I am always select one row from Table 1. Please refer my previous thread for the detailed requirement.

Jey

Niladri_Biswas

SQL Server 2005 introduced an UNPIVOT operator that will do what you're looking for.

SELECT T2.CODE, T1.COLS,T1.INDICOLVALS
FROM
(
SELECT INDICOLVALS,COLS

FROM 

     (SELECT Col1, Col2, Col3 FROM Table1) P

UNPIVOT

     (INDICOLVALS FOR COLS IN (Col1, Col2, Col3)) AS U

) AS T1

INNER JOIN TABLE2 T2

ON T1.INDICOLVALS = 10*T2.CODE

Vote me please :)

Niladri Biswas

David Mujica

I think you want to access system tables that will allow you to get the names of columns for a given table. For example, the following will give you a listing of all of the columns for "myTable1". You could then expand this query to join to your other tables.

Use myDatabase
GO

select so.name,sc.name
from sysobjects so, syscolumns sc
where so.xtype = 'U'
and so.name = 'myTable1'
and so.id = sc.id

Tell me if this helps you.

Jay_se

Great Work :) I am really thankful for your effort spent on this & guidance :) I have used this logic for my case. BUT, the JOIN is the highlighting one :) Sorry, this is for fun. ON T1.INDICOLVALS = 10*T2.CODE

declare @cols nvarchar(2000)
declare @sql nvarchar(4000)

set @cols='Col1, Col2, Col3'

set @query='SELECT T2.CODE,
T1.COLS,
T1.INDICOLVALS
FROM
(
SELECT INDICOLVALS,COLS FROM

(SELECT Col1, Col2, Col3 FROM Table1) P
UNPIVOT
(INDICOLVALS FOR COLS IN ('+ @cols +')
) AS U
) AS T1
INNER JOIN TABLE2 T2
ON T1.COLS = T2.[Name]'

--print @query

execute(@query)

As the list of columns may change in future, we have used the below script to get the list of columns and using the CURSOR, it is stored in the @cols variable with comma seperated format.

Select name from syscolumns where id=object_id('Table 1')

Once Again, Thank You very much. :)

Jey

modified on Wednesday, June 17, 2009 11:03 AM

Jay_se

Thank you Mujica. Definately It helps me... Thanks a lot. Please find my last comment for the complete solution.

Jey

Niladri_Biswas · modified on Wednesday, June 17, 2009 11:03 AM

:)

Niladri Biswas