April 1, 2008 at 8:22 am
Hey folks, I've got a small question about the best way to store/query a bit of information...
I've got the following data, it's the way it's sent to me in a bulk format. I can do with it what I please as far as remodeling it, but I'm looking for some ideas on the best(fastest in performance) and easiest way to implement it.
Say I have 2 employees making widgets for me. They each fill out a sheet with how many of each type by size and color. Obviously, there would be a date column and such to keep them separated by day/week or whatnot, but lets say I am currently just dealing with an aggregate total. It comes to me as such.
Sample Data
CREATE TABLE Test (
ID INT identity(1,1) PRIMARY KEY NOT NULL,
NAME VARCHAR(25),
l1c1 SMALLINT,
l1c2 SMALLINT,
l1c3 SMALLINT,
l1c4 SMALLINT,
l1c5 SMALLINT,
l1c6 SMALLINT,
l2c1 SMALLINT,
l2c2 SMALLINT,
l2c3 SMALLINT,
l2c4 SMALLINT,
l2c5 SMALLINT,
l2c6 SMALLINT,
l3c1 SMALLINT,
l3c2 SMALLINT,
l3c3 SMALLINT,
l3c4 SMALLINT,
l3c5 SMALLINT,
l3c6 SMALLINT)
INSERT INTO test
SELECT 'Bob', 0,1,5,1,3,0,1,3,2,5,1,0,0,0,5,2,0,2
UNION
SeLECT 'Bill', 1,5,0,0,3,1,1,0,4,0,5,0,0,2,3,0,1,0
which represents...
Bob's Widget Production
Widget |Red Black Purple|Red Black Purple
Type |SMALL |Large
-------|------------------|----------------------
Type1 |0 1 5 |1 3 0
Type2 |1 3 2 |5 1 0
Type3 |0 0 5 |2 0 2
Bill's Widget Production
Widget |Red Black Purple|Red Black Purple
Type |SMALL |Large
-------|------------------|----------------------
Type1 |1 5 0 |0 3 1
Type2 |1 0 4 |0 5 0
Type3 |0 2 3 |0 1 0
I'm attempting to find a way to store this information, and also make it easy to report back to the client. I'd love to be able to get the data into a tabular format so that I can just populate a datagrid with it easily in my presentation layer.
There will always be the same number of widget types and also the same size and color combinations, or at least so they say...
I'd thought of just keeping it in one row and renaming the columns and then manually assigning each value from each column to the appropriate field in the datagrid, but that seemed like more work than it was worth, so I was hoping for a solution that I could create in TSQL.
Yes this is Sql05.
any thought would be very helpful.
Thanks in advance.
-Luke.
April 1, 2008 at 8:28 am
My first instinct would be to normalize it.
Create a table for types and a table for colors, then have Date, Name, TypeID, ColorID and Qty in the main table. Use Pivot and aggregate functions to produce your data grid.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 1, 2008 at 8:45 am
Thanks, That was somewhat the route I was beginning to proceed down, but I wasn't sure exactly how to break up the table to create something that resembled normalized data.
I was trying to partition it either as an entire row, or an entire column, which is why I suppose I was having so much difficulty trying to get my head wrapped around it. I'd completely overlooked treating each intersection as an individual entity.
Thanks.
-Luke.
April 1, 2008 at 8:53 am
Tend to agree with GSqaured, don't forget size 🙂
No matter how you want to report it, you want to break out the repeating elements, like color, and drop them in a table with a marker to the main table.
April 1, 2008 at 9:05 am
I'll third that. Start out with a design that is as normalized as possible and make adjustments as needed.
Really, I chimed in here to give Luke a pat on the back for a well presented problem. Posting table DDL, sample table INSERTs, expected output, and a clear description of the problem domain makes good feedback easier to come by. Great job Luke!!
April 1, 2008 at 9:08 am
Luke L (4/1/2008)
Thanks, That was somewhat the route I was beginning to proceed down, but I wasn't sure exactly how to break up the table to create something that resembled normalized data.I was trying to partition it either as an entire row, or an entire column, which is why I suppose I was having so much difficulty trying to get my head wrapped around it. I'd completely overlooked treating each intersection as an individual entity.
Thanks.
-Luke.
Luke - if you get it in a denormalized format - look at using the UNPIVOT operation to "re-normalize" it.
It looks something like this:
Select [Name], Widget,Quantity
from (select [name],
l1c1 ,l1c2 , l1c3 , l1c4 , l1c5 ,
l1c6 ,l2c1 ,l2c2 ,l2c3 ,l2c4 ,
l2c5 ,l2c6 ,l3c1 ,l3c2 ,l3c3 ,
l3c4 ,l3c5 ,l3c6
from TEST) as TU
UNPIVOT (Quantity for Widget in (
[l1c1] ,[l1c2] ,[l1c3] ,[l1c4] ,[l1c5] ,
[l1c6] ,[l2c1] ,[l2c2] ,[l2c3] ,[l2c4] ,
[l2c5] ,[l2c6] ,[l3c1] ,[l3c2] ,[l3c3] ,
[l3c4] ,[l3c5] ,[l3c6])) as UNpvt
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 1, 2008 at 9:36 am
Yeah I knew I needed to normalize the data, I just wasn't sure where to start with this one.
thanks again for putting me on the right track.
@john-2, I find it a pain in the arse to answer question when you don't have enough data as well. I only try to do what I expect from everyone else.
@matt-2 thanks for tip on UnPivot, as I've never really had much cause to use the Pivot/Unpivot operators, I was about to begin doing this all by hand. that's pretty slick.
Thanks again.
-Luke.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply