Challenge with Select Query - 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)

Challenge with Select Query

Author

Topic

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2011-03-30 : 04:06:07

I have used a select query results as

Price_Act Price_FC Money_New Money_Old
Price_act 148.7774 147.1522 58721.94 59777.26
Price_FC 148.7774 147.1522 58721.94 59777.26
Money_new 148.7774 147.1522 58721.94 59777.26
Money_old 148.7774 147.1522 58721.94 59777.26

I need the above output as

Act FC
Price 148.7774 147.1522
Money 58721.94 59777.26

Please help me in this. Very urgent...

Regards,
Kalaiselvan R
Love Yourself First....

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-03-30 : 04:13:53

In the desired results you list 2 column headers but three columns. Which?

What's the logic behind the values? What determines what goes into what column?

--
Gail Shaw
SQL Server MVP

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-30 : 04:24:53

- please use [code] tag to format the data

- can you explain how the required output come about ?

- you might want to re-post a more meaningful sample data, the sample data that you posted have only 2 distinct value. Without clear explanation, it is very hard for anyone to guess what do you want.

KH
[spoiler]Time is always against us[/spoiler]

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2011-03-30 : 04:25:58

Groups Price_Act Price_FC Money_New Money_Old
Price_act 148.7774 147.1522 58721.94 59777.26
Price_FC 148.7774 147.1522 58721.94 59777.26
Money_new 148.7774 147.1522 58721.94 59777.26
Money_old 148.7774 147.1522 58721.94 59777.26

Groups Act FC
Price 148.7774 147.1522
Money 58721.94 59777.26

Now its clear.

Regards,
Kalaiselvan R
Love Yourself First....

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-30 : 04:33:55

Still not very clear.

In your output, you have Price 148.7774 under the Act column, i know it is coming from the Price_Act column of the source table but which row ?

And how about money ? there isn't a Act & FC in the source table

KH
[spoiler]Time is always against us[/spoiler]

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2011-03-30 : 04:47:57

SELECT * FROM dbo.M_GroupingValues Where CountryID=1 and CategoryID in (1)

Output:
[ValueNAmes] [Country] [Category]
Volume_actual 1 1
Volume_old_forecast 1 1
Price_actual 1 1
Price_old_forecast 1 1
Money Supply_new 1 1
Money Supply_old 1 1
----------------------------------------------------------------------
SELECT Country,Category,Volume_actual,Volume_old_forecast,Price_actual,Price_old_forecast,[Money Supply_New],[Money Supply_old]
FROM M_Working Where Country=1 and Category=1 and UploadQuarterId=2 and Quarter=3 and Year=10

Output:
[Country] [Category] [Volume_Actual] [Volume_Old_Forecast]
1 1 158174416 158619377
[Price_Actual] [Price_Old_Forecast] [Money Supply_New]
148.7774 147.1522 58721.94
[Money Supply_Old]
59777.26

From the above selected Queries i need the output as below:

[ValueNames] [Actual] [Forecast]
Volume 158174416 158619377
Price 148.7774 147.1522
Money Supply 58721.94 59777.26

Here the Valuenames column comes from 1st select query (where as Volume_Act and Volume_Forecast must come as Volume) repeats for others

And Actual value and Forecast value comes from 2nd Select query related Actual and Forecast amount. Money Supply_Newis actual and Money Supply_Old is forecast.

Is this clear now, revert me back if any calrifications. But am in urgent to finish this..

Regards,
Kalaiselvan R
Love Yourself First....

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-30 : 05:00:37

try


SELECT	g.ValueNAmes, v.ACT, v.FC
FROM	(
		SELECT	DISTINCT
			ValueNAmes	= LEFT(ValueNAmes, charindex('_', ValueNAmes) - 1), 
			Country, 
			Category
		FROM	M_GroupingValues
		WHERE	Country		= 1
		and	Category	= 1
	) g
	INNER JOIN
	(
		SELECT 	Country, Category, Volume_actual as ACT, Volume_old_forecast as FC
		FROM 	M_Working 
		Where 	UploadQuarterId=2 and Quarter=3 and Year=10
		union all
		SELECT 	Country, Category, Price_actual as ACT, Price_old_forecast as FC
		FROM 	M_Working 
		Where 	UploadQuarterId=2 and Quarter=3 and Year=10
		union all
		SELECT 	Country, Category, [Money Supply_New],[Money Supply_old]
		FROM 	M_Working 
		Where 	UploadQuarterId=2 and Quarter=3 and Year=10
	) v	on	g.Country	= v.Country
		and	g.Category	= v.Category

KH
[spoiler]Time is always against us[/spoiler]

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2011-03-30 : 05:57:38

Khtan,
Not bad. But wat u have replied is already done here. But the output will be repeated for all columns and rows.

For your Query Output comes as :

[Value Names] [Actual] [Forecast]
Money Supply 158174416 158619377
Price 158174416 158619377
Volume 158174416 158619377
Money Supply 148.7774 147.1522
Price 148.7774 147.1522
Volume 148.7774 147.1522
Money Supply 58721.94 59777.26
Price 58721.94 59777.26
Volume 58721.94 59777.26

But i need the Output as:

[Value Names] [Actual] [Forecast]

Volume 158174416 158619377
Price 148.7774 147.1522
Money Supply 58721.94 59777.26

Regards,
Kalaiselvan R
Love Yourself First....

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-30 : 06:31:26

[code]
SELECT g.ValueNAmes, v.ACT, v.FC
FROM (
SELECT DISTINCT
ValueNAmes = LEFT(ValueNAmes, charindex('_', ValueNAmes) - 1),
Country,
Category
FROM M_GroupingValues
WHERE Country = 1
and Category = 1
) g
INNER JOIN
(
SELECT Country, Category, Volume_Actual as ACT, Volume_Old_Forecast as FC, ValueNAmes = 'Volume'
FROM M_Working
Where UploadQuarterId=2 and Quarter=3 and Year=10
union all
SELECT Country, Category, Price_Actual as ACT, Price_Old_Forecast as FC, ValueNAmes = 'Price'
FROM M_Working
Where UploadQuarterId=2 and Quarter=3 and Year=10
union all
SELECT Country, Category, [Money Supply_New], [Money Supply_Old], ValueNAmes = 'Money Supply'
FROM M_Working
Where UploadQuarterId=2 and Quarter=3 and Year=10
) v on g.Country = v.Country
and g.Category = v.Category
and g.ValueNAmes = v.ValueNAmes
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources