Query for Behind Column Values

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)

Query for Behind Column Values

Author

Topic

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2012-01-10 : 09:11:53

TableA:
[ID] [Actual_Column] [Temp_Column] [Value]
1 First Q1 -
2 Second(A) Q2(a) 1
3 Second(B) Q2(b) 2
4 Second(C) Q2(c) 3
5 Third Q3 -

TableB:
[QID] [Q1] [Q2(a)] [Q2(b)] [Q2(c)] [Q3]
1 Idea 1 - - Idea1
2 RPG - 1 1 RPG1
3 TATA - - 1 TATA1
4 Uninor 1 1 1 Uninor1
5 Cell 1 - 1 Cell1

In 1st table temp columns belongs to 2nd table columns.
And sub Questions values were defined in 1st table with sample values.
Need a Query to built the following results.

Expected Result:
[First] [Second(A)] [Second(B)] [Second(C)] [Third]
Idea 1 - - Idea1
RPG - 2 3 RPG1
TATA - - 3 TATA1
Uninor 1 2 3 Uninor1
Cell 1 - 3 Cell1

The above result comes from 2nd table and its value will change according to the value in 1st table.
Please help me to provide this result...

Regards,
Kalaiselvan R
Love Yourself First....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-10 : 10:36:57

[code]

create table #p
(
[ID] int,
[Actual_Column] varchar(100),
[Temp_Column] varchar(100),
[Value] int
)
INSERT #p
SELECT 1, 'First', 'Q1',null union all
SELECT 2, 'Second(A)', 'Q2(a)', 1 union all
SELECT 3, 'Second(B)', 'Q2(b)', 2 union all
SELECT 4, 'Second(C)', 'Q2(c)', 3 union all
SELECT 5, 'Third', 'Q3',null

create table #q
(
[QID] int,
[Q1] varchar(100),
[Q2(a)] varchar(100),
[Q2(b)] varchar(100),
[Q2(c)] varchar(100),
[Q3] varchar(100)
)
insert #q
SELECT 1, 'Idea', 1, '-', '-', 'Idea1' union all
SELECT 2, 'RPG','-' , 1, 1, 'RPG1' union all
SELECT 3, 'TATA', '-', '-', 1, 'TATA1' union all
SELECT 4, 'Uninor', 1, 1, 1, 'Uninor1' union all
SELECT 5, 'Cell', 1, '-', 1, 'Cell1'
select [First], [Second(A)] ,[Second(B)], [Second(C)], [Third]
from
(
select p.[Actual_Column],COALESCE(NULLIF(CASE WHEN ISNUMERIC(m.val)=1 AND m.val<> '-' THEN CAST(m.val * p.[Value] AS varchar(10)) ELSE CAST(m.val AS varchar(10)) END,'0'),'-') AS val,QID
from
(
select *
from #q
unpivot ( val for cat in ([Q1], [Q2(a)] ,[Q2(b)], [Q2(c)] ,[Q3]) )u
)m
inner join #p p
on p.[Temp_Column] = m.cat
)r
pivot (max(val) for [Actual_Column] in ([First], [Second(A)] ,[Second(B)], [Second(C)], [Third] ))pvt

drop table #p
drop table #q

output
----------------------------------
First Second(A) Second(B) Second(C) Third
Idea 1 - - Idea1
RPG - 2 3 RPG1
TATA - - 3 TATA1
Uninor 1 2 3 Uninor1
Cell 1 - 3 Cell1

[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources