June 1, 2011 at 2:40 pm
Hi!!!
I must to group records from a table and i dont know how.
My table is something like this:
Column 1 Column 2 Column 3
A Step 1 Value
A Step 2 Value
B Step 1 Value
B Step 2 Value
B Step 3 Value
C Step 1 Value
C Step 2 Value
And I need this
Column 1 Step_1_Value Step_2_Value Step_3_Value
A Value Value Value
B Value Value Value
C Value Value Value
How can I do it?? Could anyone sugest a query please??
Regards
June 1, 2011 at 2:51 pm
June 1, 2011 at 5:13 pm
Jeff's article is very nice, but I'll try to give you a direct answer in much simpler form.
In the result, letters A,B,C are unique per row. So, you have to group by Column1 to achieve that, to condense the rows to unique values of Column1.
Then, in the next column of the result you have to extract the value associated with "Step 1" rows of current letter. We will use CASE expression to filter only values we are interested in. Theoretically, there can be more than one row associated with Step 1 and current letter, so you have to use some of the aggregate functions, like MAX or MIN, around our CASE expression.
Here it is:
SELECT t.Column1,
Step_1_Value = MAX( CASE WHEN t.Column2 = 'Step 1' THEN t.Column3 END ),
Step_2_Value = MAX( CASE WHEN t.Column2 = 'Step 2' THEN t.Column3 END ),
Step_3_Value = MAX( CASE WHEN t.Column2 = 'Step 3' THEN t.Column3 END )
FROM dbo.MyTable t
GROUP BY t.Column1
June 2, 2011 at 10:14 am
Thank you guys!!
The article and the example were very useful to me. I got the result expected.
Just one more doubt. Can I use the DISTINCT function instead of MAX or MIN?? Is it diferent the result if i do that??
Regards!!
June 2, 2011 at 1:31 pm
You condense group of rows to one row. You can get max, min, avg, count(*) etc.
List of all aggregate functions is in t-sql reference:
http://msdn.microsoft.com/en-us/library/ms173454.aspx
You can use distinct only within count, like this: "count(distinct myColumn)".
HTH,
Vedran
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply