Pivot or switch?

anthukne · #1 (**permalink**) 06-22-12, 15:57

Hi all,,

this is my first post so please bare with me. If its something stupid please let me know and point to me to any links you think suitable.

I currently have a query to do some inner joins on multiple tables.

This is my sql Query im using

Code:

Select
 
                    agent_login_dbid,
                    P.last_name,
                    P.first_name,
                    P.user_name,
                    AL.login_code,
                    S.name,
                    SL.level_

                    From dbo.cfg_person P
 
                    INNER JOIN dbo.cfg_login_info LI ON P.dbid = LI.person_dbid
                    INNER JOIN dbo.cfg_agent_login AL ON LI.agent_login_dbid = AL.dbid
                    INNER JOIN dbo.cfg_skill_level SL ON P.dbid = SL.person_dbid
                    INNER JOIN dbo.cfg_skill S ON SL.skill_dbid = S.dbid"

Unfortunately I will never know how many "name" 's that will be returned could be 1 upto 100

However when my data is returned it returns it to multiple rows like the following

Before - http://img832.imageshack.us/img832/2672/beforex.jpg

and i would like to add to columns dynamically like the following.

After - http://img823.imageshack.us/img823/1611/afterys.jpg

If you can help, thank you

Pat Phelan · #2 (**permalink**) 06-22-12, 17:06

Your links aren't useful, I can't resolve them.

The short answer boils down to the old adage "Mange data with data managment tools, present data with Data presentation tools". Keep the result set consistant with a fixed number of consistantly named columns. Decide how to present the data in your presentation layer (which is a lot different on a web page than on a tablet or cell phone), and present the data appropriately based on the presentation device.

-PatP

anthukne · #3 (**permalink**) 06-22-12, 17:31

Hi,

I have attached the before and after images.

Also just to let you know i need it as a query as this runs in C#

Thanks

Pat Phelan · #4 (**permalink**) 06-22-12, 17:34

The images you've posted confirm my previous statement.

-PatP

anthukne · #5 (**permalink**) 06-23-12, 07:04

im calling this from C#

Am i better off trying to manipulate it from there rather than the SQL Query?

Pat Phelan · #6 (**permalink**) 06-23-12, 14:40

I think that you are definitely better off doing the pivot in your C# code than in your Transact-SQL code. The C# code can then have a consistant input result set (fixed number of columns and data types) and massage that data as needed to meet your display requirements.

It isn't hard to pivot the data in Transact-SQL, but it is quite challenging to write a C# program to handle result sets that are inconsistant and it is harder still to adapt the C# later to present the variable result set data differently such as presenting a pie chart instead of a tablular form!

-PatP

Internet Services

Web Development

Digital Marketing

Consumer Tech