data alignment

Question

data alignment

Anthony Hires

SSC Rookie

Points: 26
More actions
July 30, 2007 at 2:06 pm

#92571

Thanks In advance.
I'm writing a report using VS2005 CR and I find myself stuck. I have data in a table that looks sort of like this:
id user test grade status statisfy
1 12 pretst 30 complete good
2 12 posttst 50 incomplete bad
3 14 pretst 45 complete good
4 14 postst 50 incomplete good

And I want it in the report to look like:
id pretst-grade pretst-status pretst-satisfy posttst-status postst-grade ...
12 30 complete good incomplete 50 bad
14 45 complete good incomplete 50 good
Is there a way to accomplish this without creating a temp table and populating it with several queries. This is a simplified version of what I need to do.
Thanks again

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004648 More actions · Answer 1

--===== Setup a test table to demonstrate the solution.
     -- THIS IS NOT PART OF THE SOLUTION!!! It's just test data!
DECLARE @yourtable TABLE
        (ID INT PRIMARY KEY, [User] INT, Test VARCHAR(20), Grade INT, Status VARCHAR(20), Satisfy VARCHAR(10))

 INSERT INTO @yourtable
        (ID, [User], Test, Grade, Status, Satisfy)
 SELECT '1','12','pretst','30','complete','good' UNION ALL
 SELECT '2','12','posttst','50','incomplete','bad' UNION ALL
 SELECT '3','14','pretst','45','complete','good' UNION ALL
 SELECT '4','14','posttst','50','incomplete','good'

--===== Now, demonstrate the solution
 SELECT pre.[User]   AS ID,
        pre.Grade    AS [Pretst-Grade],
        pre.Status   AS [Pretst-Status],
        pre.Satisfy  AS [PreTst-Satisfy],
        post.Grade   AS [Posttst-Grade],
        post.Status  AS [Posttst-Status],
        post.Satisfy AS [Posttst-Satisfy]
   FROM @yourtable pre
   LEFT OUTER JOIN
        @yourtable post
     ON pre.[User] = post.[User]
  WHERE pre.Test   = 'pretst'
    AND post.Test  = 'posttst'

To understand how this was done, lookup "joins-SQL Server" and then find "table joined to itself" under that. Might also want to take a look at "Outer Joins", as well.

Also, notice how I created the test data and test table? Would be really helpful if all posters created data like that in their post to help us help you much faster... if we don't have to concentrate on creating what you should have already setup as a test, we can concentrate on the solution a whole lot more. Also gives people the incentive to do your post before others because we know it's going to be a bit easier.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SwePeso SSC-Dedicated Points: 39758 More actions · Answer 2

And if you prefer just one table scan,

SELECT [User],

MAX(CASE WHEN Test = 'PreTst' THEN Grade END) AS PreTst_Grade,

MAX(CASE WHEN Test = 'PreTst' THEN Status END) AS PreTst_Status,

MAX(CASE WHEN Test = 'PreTst' THEN Satisfy END) AS PreTst_Satisfy,

MAX(CASE WHEN Test = 'PostTst' THEN Grade END) AS PostTst_Grade,

MAX(CASE WHEN Test = 'PostTst' THEN Status END) AS PostTst_Status,

MAX(CASE WHEN Test = 'PostTst' THEN Satisfy END) AS PostTst_Satisfy

FROM @YourTable

GROUP BY [User]

ORDER BY [User]

N 56°04'39.16"
E 12°55'05.25"

Anthony Hires SSC Rookie Points: 26 More actions · Answer 3

Thanks for your reply. My actual table is far more complex and I'm still working through the solution. However, it does seem to be working. When you refered to "joins-SQL Server" were you referring to an artical somewhere? I haven't been able to locate it. Your solution appears to work but I don't understand it completely yet.

Thanks

Jeff Moden SSC Guru Points: 1004648 More actions · Answer 4

Heh... nicely done, Peter... forgot all about our mutual friend, Mister Cross-Tab.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004648 More actions · Answer 5

Yes... they are articles... but you don't have to go far to find them. When you installed the client side of SQL Server, you should have also installed "Books Online". There's several ways to find it but I generally tell people to click on [Help] in Query Analyzer and then select [Transact-SQL Help]... the Index tab seems most useful once you get the feel for how to look for things.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)