need help on T-sql 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 2012 Forums

Transact-SQL (2012)

need help on T-sql query

Author

Topic

langthanh
Starting Member

4 Posts

Posted - 2014-01-20 : 19:40:26

Hi evey one

i need help to write a t-sql query , let me explain my problem :

i have a table like this :

Name Avg_ag Min_ag Max_ag Std_ag Avg_arsenic Min_arsenic Max_arsenic Std_arsenic
Group1 1 2 3 4 2 5 6 9
Group2 2 4 5 6 2 2 3 5
Group3 4 6 8 2 3 6 2 4

Where Avg_ag is stand for average of ag
Min_ag is for min of ag
Max_ag is for Max of ag
std_ag is for sdtev of ag

Now i want to write a query to get the result like this :

Name Agg_Name Ag Arsenic
Group1 Avg 1 2
Min 2 5
Max 3 6
std 4 9
Group2 Avg 2 2
Min 4 2
Max 5 3
Std 6 5
Group 3 Avg 4 3
Min 6 6
Max 8 2
Std 2 4

I really appreciate if anyone can help me with this problem

Thanks

nagino
Yak Posting Veteran

75 Posts

Posted - 2014-01-20 : 21:38:57

Following is help for you?
----------------------------------------------------

SELECT
	UP_AG.[Name],
	LEFT(UP_AG.Agg_Name_Base, 3) Agg_Name,
	Ag,
	Arsenic
FROM #YourTable
UNPIVOT (Ag FOR Agg_Name_Base in (Avg_ag, Min_ag, Max_ag, Std_ag))UP_AG
LEFT JOIN (
	SELECT
		UP_ARSENIC.[Name],
		LEFT(UP_ARSENIC.Agg_Name_Base, 3) AggName,
		Arsenic
	FROM #YourTable
	UNPIVOT (Arsenic FOR Agg_Name_Base in (Avg_arsenic, Min_arsenic, Max_arsenic, Std_arsenic))UP_ARSENIC
	)ARSENIC
	ON UP_AG.Name = ARSENIC.Name
	AND LEFT(UP_AG.Agg_Name_Base, 3) = AggName

-------------------------------------
From Japan
Sorry, my English ability is limited.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-21 : 05:53:11

Keep it simple


SELECT Name, 'Avg' AS Agg_name,Avg_ag AS [ag],Avg_arsenic AS [arsenic],1 AS Ord
FROM Table
UNION ALL
SELECT Name, 'Min',Min_ag ,Min_arsenic ,2
FROM Table
UNION ALL
SELECT Name, 'Max',Max_ag ,Max_arsenic ,3
FROM Table
UNION ALL
SELECT Name, 'Std',Std_ag ,Std_arsenic ,4
FROM Table
ORDER BY Name,Ord

then your desired presentation can be obtained using tools like SSRS and property is called hide duplicates applied to required textbox (Name in this case)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

langthanh
Starting Member

4 Posts

Posted - 2014-01-21 : 11:04:39

thank to all your help

i try visakh16 solution and it working !

thanks visakh16

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources