Rows to Columns (The simplest way please)

Question

Post reply

Rows to Columns (The simplest way please)

SQL_Enthusiast

Hall of Fame

Points: 3805
More actions
July 12, 2010 at 6:43 pm

#224695

Sorry guys and gals, I have no idea where to begin on this...
let me try the explain it the best I can. I have 746 active security points in my database. Let's say they are stored in TABLE_A. When I write a simple SELECT * FROM TABLE_A I get 18212 rows. That's because each security group can have any combination of security points. Therefore, a section of my results pane looks like this...
grp_code module security_pt
0103 ENC_VIEWER
0103 ENC_VIEWERASSESS
0103 ENC_VIEWERENC_ADMIN
0103 ENC_VIEWERENC_EDITMODE
0103 ENC_VIEWERENC_PRINT
What I want to see is...
grp_code module secruity_pt secruity_pt secruity_pt
0103 ENC_VIEWER ASSESS ENC_ADMIN
Basically, 1 row for the grp_code, each module (21 of them) in it's own column, and each security_pt in it own column.
I have already tried the MAX(CASE... and that worked. Unfortunately, the means I have to case EVERY security point (all 746) for an unstable table. Meaning, our next release in two weeks has 9 new points added. It will continue to grow, so I need a statement to grow with it... Any thoughts...
(Please remember, I am still learning so the more technical the response, the more likely I going to reply with more questions. Thank you in advance. :hehe:

Viewing 12 posts - 1 through 11 (of 11 total)

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

Please see the Cross-Tabs and Pivots, Part 1 and Part 2 links in my signature.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

USe pivot table approach

USE DOMAIN_DBA

GO

DECLARE @t TABLE (EMP_ID int , Contact_type nvarchar(50), Contact_Number nvarchar(300))

INSERT INTO @t

SELECT 1 ,'Home' ,'92 - 51 - 225478'

UNION SELECT 1, 'Cell', '92 - 131 - 225478'

UNION SELECT 1, 'Office', '92 - 51 - 325478'

UNION SELECT 2, 'Home', '92 - 41 - 225478'

UNION SELECT 2, 'Cell', '92 - 131 - 225478'

UNION SELECT 2, 'Office', '92 - 41 - 325478'

UNION SELECT 2, 'Fax', '93 - 41 - 325528'

select emp_id, ISNULL([cell],'') as [cell], ISNULL([mobile],'') [mobile],ISNULL([office],'') [office],ISNULL([fax],'') [fax]

from

(

SELECT EMP_ID, Contact_type , Contact_Number

FROM @T

) t

PIVOT

(max(contact_number) FOR Contact_type IN ( [cell], [mobile],[office],[fax])

) as pvt

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

SQL_Enthusiast Hall of Fame Points: 3805 More actions · Answer 3

Thank you for posting the code. I need to ask you another question about htis topic. It appears that you are inserting data into the table.

INSERT INTO @t

SELECT 1 ,'Home' ,'92 - 51 - 225478'

UNION SELECT 1, 'Cell', '92 - 131 - 225478'

UNION SELECT 1, 'Office', '92 - 51 - 325478'

UNION SELECT 2, 'Home', '92 - 41 - 225478'

UNION SELECT 2, 'Cell', '92 - 131 - 225478'

UNION SELECT 2, 'Office', '92 - 41 - 325478'

UNION SELECT 2, 'Fax', '93 - 41 - 325528'

But I cannot do this for two reasons. One, I currently have 746 columns that would have to be in the INSERT and then in the PIVOT. Two, the table will continue to grow as we add new points to it. Like I stated earlier, I have 9 new points coming out next month. I don't want to have to build on my statement after it is released. I want it to dynamically grow based on the table. Any other thoughts on how to do this???:w00t:

Lowell SSC Guru Points: 323518 More actions · Answer 4

well...you're going to present someone with a grid with 700 + columns? why? I would simply NOT do that.

maybe a redesign should be called upon to revisit why the data is stored one way but you need it the other. maybe you are overstating your example a bit?

typically you really only need some data pivoted, and to every possible value, but your situation may be different.

search the forums for "dynamic pivot" which will do what you are after by creating a statement and using dynamic SQL to execute it.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQL_Enthusiast Hall of Fame Points: 3805 More actions · Answer 5

Lowell,

I'm not going to argue with you about giving the customer 700+ columns... But unfortunately, I have to give the customer what they want when they are the ones keeping the lights on. There spec is very clear on this.

We want the following security points report.

All security Module/Point is to remain on one row for each user.

Am I exaggerating, nope... I have 22 modules and each module has a subset of points. One module has 102 lists. Each list has 4 points (Add, Update, Delete, View). that's 408 right there... Security setup in our system is not joke. Each button, action, or event has a security point to Add, Update, Delete, or View... Some actions even have 6-8 points each... It just depends on the severity of the module.

LutzM SSC Guru Points: 107049 More actions · Answer 6

Did you make your customer aware of the consequence?

I'm not sure if that's what's intended. I'd recommend asking for clarification.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Lowell SSC Guru Points: 323518 More actions · Answer 7

from a security presentation standpoint, I'd think that seeing 22 rows one for each security module, and having usually 4 security points, but sometimes 7 or 8 points would be a much better and more manageable presentation.

sounds like you are taking the clients stated requirement literally "they said they wanted...) and not working out a manageable presentation layer....i know I've seen that happen in my shop, and it gets feature creep-ed.. "that's what i said, but not what i wanted...we were depending on your expertise"...and we ended up redoing the functionality a few iterations.

I'd probably present it in some color coded presentation instead....list all users, with 22 columns, one for each module...read only is gray, read write another color...double clicking on a row presents the details with one user featuring 22 rows

so in that case i might have 22 views that do the pivots for me, one for each module....easy to access that way.

remember business requirements come from end users who are NOT programmers...when they say "i want to see it all", they don't mean "all' the same literal if-then-else way you think of it.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323518 More actions · Answer 8

also consider this: change the presentation so it is not user centric...make it functionality centric.

a tree view with branches and sub branches of your modules and sub modules would probably be better....then when you select the branch of the tree, you can present the users/roles that have rights to that branch.

gets completely away from the 700 column model and is much more manageable...when i get to work tomorrow i'll screenshot what our security tree looks like..might give you some ideas....

the security people usually what to know who has access to which module, so showing the module and the users as it's children is better the opposite showing a user and the modules he has access to as it's children, where most modules are no access anyway.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQL_Enthusiast Hall of Fame Points: 3805 More actions · Answer 9

Thank you Lowell for all the help. I'm still at the "drawing board" phase of this project, so I look forward to seeing your example and building from there. Luckily I just gain some time since the customer has a new release to QA and deploy. Thank you again for all the help! 🙂

DataDog Hall of Fame Points: 3305 More actions · Answer 10

provide a users explorer - one record per user

link to rights explorer one record per right filtered by 1 user

then provide a rights explorer - one record per right

link to users explorer / one record per user filtered by 1 right

(also cross link the above 2)

this will solve most basic needs

another good trick to manage security complexity is either

to use both user groups *and* rights groups

so you can assign sets of rights (a rights group) to a sets of users (a user group)

or you can derive this perspective by grouping all the assigned rights into patterns

this way it can be easy to spot an errant user etc.

to clarify: calculate a list of distinct rights assignments and then link from this to users

this can bring huge complexity back into the range of human comprehension quickly

a similar model is the .NET Code Access Security // see windows / Administration Tools / Microsoft .NET Framework 2.0 Configuration

Praveen Goud Kotha Ten Centuries Points: 1141 More actions · Answer 11

Hi..

Even though it looks odd (i.e..showing above 700 columns in a grid)

but if the client requirement doesn't change..

then we can easily achieve the desired output result as mentioned above by using the

"DYNAMIC PIVOT" Concept..

[font="Comic Sans MS"]Praveen Goud[/font]