Need some help with SQL 1

[jayj24]

I have a table with 2 columns

AccountID Events

111111 Movies
111111 Concert
111111 Basketball
222222 movies
333333 basketball
222222 football
111111 concert
222222 movies
333333 sports



Basically something of that sort is what i have my practice table setup as. And well what i would like to do is figure out how to write a query that grabs one account id and list all its events in one row such as this


AccountID Event1 Event2 Event3 Event4

111111 movies basketball concert concert
222222 movies football movies
333333 sports basketball



Is that possible and if so where would i start to start reading up on some stuff on how to accomplish this.

Thanks for any help in advance

 

[r937]

that's called a "crosstab" layout

unless you're using microsoft access, which has this functionality built into its front end, you should ~not~ be doing this with SQL, but rather, with your front end application language (asp or php or whatever you're using)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[jayj24]

Appreciate the response

but with a crosstab layout I think you would have dropdown list instead of it auto populating

I will read more up on crosstab layouts

thanks again

 

[CaptainD]

Take a look at this, you might be able to make it work, you did not specify what SQL server version you are using.

(MS SQL server 2005 and above, I believe, have crosstab functions.)

http://www.sqlmag.com/Articles/ArticleID/23162/23162.html?Ad=1

 

[markros]

If you know that you never would have more than 4 events per group, then it's quite easy using RowNumber() function in SQL Server 2005 and up, e.g.

;with cte as (select AccountID, Events, RowNumber() over (partition by AccountID order by Events) as rn from myTable)

select C1.AccountID, C1.Events as Event1, C2.Events as Event2, C3.Events as Event3, C4.Events as Event4 from cte C1 left join cte C2 on C1.AccountID = C2.AccountID and C1.RowNumber = 1 and C2.RowNumber = 2 left join ...

Or you may try using Pivot (and dynamic Pivot) for your problem.

 

[markros]

See also

http://social.msdn.microsoft.com/Fo...l/thread/93271733-090b-4bec-b00d-e6c1364b488f

for a very similar problem.

 

[jayj24]

Thanks again for the replies it is some good reading

I will mess with this stuff some more because i was trying to put myself in a situation like I ran across a badly programed table and had to restructure it and it had over 20000 records.

Trying to learn more thats all.

 

[JeffModen]

@jayj24

The structure of the original table is better (ie: normalized) than the cross tab version of the table.

Also, you don't need to self join the cte. Instead, use the classic structure of a character based cross-tab. They eventually get to the same thing in the link that markros offered.

--===== Create a demonstration table and populate it with the
     -- data from the original post.
     -- This is NOT a part of the solution.
 CREATE TABLE dbo.YourTable (AccountID INT, Events VARCHAR(20));
 INSERT INTO dbo.YourTable
        (AccountID, Events)
 SELECT 111111,'Movies' UNION ALL
 SELECT 111111,'Concert' UNION ALL
 SELECT 111111,'Basketball' UNION ALL
 SELECT 222222,'movies' UNION ALL
 SELECT 333333,'basketball' UNION ALL
 SELECT 222222,'football' UNION ALL
 SELECT 111111,'concert' UNION ALL
 SELECT 222222,'movies' UNION ALL
 SELECT 333333,'sports';

--===== Create a classic character based cross-tab from the test table above.
WITH cteNumberEvent AS
(
 SELECT AccountID,
        ROW_NUMBER() OVER (PARTITION BY AccountID
                               ORDER BY AccountID, Events) AS EventNumber,
        Events
   FROM dbo.YourTable
)
 SELECT AccountID,
        MAX(CASE WHEN EventNumber = 1 THEN Events ELSE NULL END) AS Event01,
        MAX(CASE WHEN EventNumber = 2 THEN Events ELSE NULL END) AS Event02,
        MAX(CASE WHEN EventNumber = 3 THEN Events ELSE NULL END) AS Event03,
        MAX(CASE WHEN EventNumber = 4 THEN Events ELSE NULL END) AS Event04,
        MAX(CASE WHEN EventNumber = 5 THEN Events ELSE NULL END) AS Event05
   FROM cteNumberEvent
  GROUP BY AccountID;

If you suspect the need for more columns but don't know how many there will be, a dynamic solution can be provided.

Also, as a sidebar, how many events are in each row in the original table. Just one, right? So why is the column named "Events"? You didn't name the column that holds account numbers "AccountIDS"... why pluralize the "Event" column?

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row

 

[markros]

Jeff,

You're right - it's simpler and we don't need a self-join.

 

[JeffModen]

Thanks for the feedback, Markros.

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row

 

[jayj24]

Thanks Jeff and in the orginal database its not events i just typed it that way to give an example of what my issue was.

The orginal table as over 25000 records and it only has one event per column.


Thanks for all the feed back im going to give that a try Jeff and see how it works. Appreciate everything

 

[JeffModen]

Thank you for the feedback.

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row

 

[Emtucifor]

Just to jump on the bandwagon... it sounds like your original table IS properly designed. Just the fact that each row of your desired output has a different number of events proves this. While it may be "harder" to query the way you want to now, all the other (and way more important) queries for working with the data would become far more difficult if you pivoted your original source data table.

Imagine:

--Now
SELECT DISTINCT AccountID FROM Events WHERE EventType IN ('Basketball', 'Movies')

--After pivoting:
SELECT AccountID
FROM Events
WHERE
   Event1 IN ('Basketball', 'Movies')
   OR Event2 IN ('Basketball', 'Movies')
   OR Event3 IN ('Basketball', 'Movies')
   OR Event4 IN ('Basketball', 'Movies')
   OR Event5 IN ('Basketball', 'Movies')
   OR Event6 IN ('Basketball', 'Movies')
   OR Event7 IN ('Basketball', 'Movies')
   OR Event8 IN ('Basketball', 'Movies')
   OR Event9 IN ('Basketball', 'Movies')
   OR Event10 IN ('Basketball', 'Movies')
   OR Event11 IN ('Basketball', 'Movies')
   OR Event12 IN ('Basketball', 'Movies')
   OR Event13 IN ('Basketball', 'Movies')
   OR Event14 IN ('Basketball', 'Movies')
   OR Event15 IN ('Basketball', 'Movies')
   OR Event16 IN ('Basketball', 'Movies')

And even with that piece of junk, are you SURE you'll NEVER have more than 16 per account? Ever? Willing to stake your life on it?

What if you deactivate the Event type "Badminton". Can you construct a query that will easily remove the "Badminton" values?

--Now
DELETE Events WHERE EventType = 'Badminton'

--After pivoting
UPDATE Events
SET
   Event1 = CASE WHEN Event1 = 'Badminton' THEN NULL ELSE Event1 END,
...
   Event16 = CASE WHEN Event16 = 'Badminton' THEN NULL ELSE Event16 END
-- Plus, what if you want to remove the holes left behind? Can you even begin to imagine a query for that?

I hope this helps demonstrate that your table is properly designed.

 

[chandler]

I have a similar data layout for some of our shop operations. I basically created a view in the cross-tab looking format and then query that with a web service for my client app. However, how do I go about updating the events?

In my client app each "record" has a field for every event and I need to be able to update any changes to any of the events. (instead of event types I've got dates.)

Off the top of my head, I was thinking I would need my save button click event to loop through and fire off an update query for every event field. But I'm sure I can accomplish the same thing with a single query.. just can't get my head wrapped around how to do it.

Thanks.



Chandler
I ran over my dogma with karma!