Flatten data with Column name

Transact-SQL
1

I have 2 tables (product, productfinish) from which I want to create a view

product:
product size
70A 3" x 12"
70A 3 1/2" x 15"

productfinish (the last 3 columns are finish columns):
product size US26D US26 US32D
70A 3" x 12" 10 12 8
70A 3 1/2" x 15" 15 20 10

I would like to write a query to flatten as the following where the finish field = fieldname starting from column 3 of productfinish, the price field = the value in the finish column.

The results would look like this:

product size Finish Price
70A 3" x 12" US26D 10
70A 3" x 12" US26 12
70A 3" x 12" US32D 8
70A 3 1/2" x 15" US26D 15
70A 3 1/2" x 15" US26 20
70A 3 1/2" x 15" US32D 10

Thank you in advance
Nick

2

Would you be ok with a design change recommendation?

3 
SELECT pf.product, pf.size, flatten.Finish, flatten.Price
FROM dbo.productfinish pf
CROSS APPLY (VALUES('US26D', pf.US26D),('US26',pf.US26),('US32D',pf.US32D)) 
    AS flatten(Finish, Price)
1 Like
4

Yes, I am interested in hearing design change recommendations. Thanks

5
  • what are these columns? US26D US26 US32D, are these finish types? can you explain their meaning, is it industry standard finish types or your own in house finish types for the products you guys produce?
  • Can a product have more than one size? Can it come in different sizes.
  • Are you interested in querying your data for products of the same size. IE what size sales the most, what size sales the least etc
  • do sizes come in pairs as you have them here x " by y" or do you have other sizes like x" by y" by z"
6

These columns US26D US26 US32D are industry standard finishes

A product can come in multiple finishes.

The sizes come as x by y and also x by y by z

We are trying to flatten out the above 2 tables into a product database which contains product, size, finish and price columns. Scott Pletcher's solution was very helpful in solving this. However, I am interested in your suggestions to optimize it.

Thanks,
Nick

7

do the prices for the finishes fluctuate in the span of a year, within a fiscal year, or they could change once a year? how often can finish prices change? and are you interested in tracking price changes?
also x by y by z would that height, width, length

here is a normalized design. by no means is this me saying you have to do it this way, but in the long run it can gracefully handle changes in your products. For example if a new type of finish comes there is not need to add new finish column and price. you just add this new finish type and associate it to the products that come in that finish.

create table products(productid int not null identity(1,1), productpartnumber varchar(50) )
create table productsizes(productid int not null, productsize varchar(50) )
create table finishtypes(finishtypeid int not null identity(1,1), finishtype varchar(50), finishtypedescr varchar(150) )
create table productfinishes(productid int not null, finishtypeid int not null)

insert into finishtypes(finishtype)
select 'US26D' union
select 'US26' union
select 'US32D'

insert into products
select '70A'

insert into productsizes
select productid, ps.productsize from products
cross apply (select '3" x 12"' as productsize union 
             select '3 1/2" x 15"'
			 ) ps

insert into productfinishes
select productid, ft.finishtypeid from products
cross apply (select finishtypeid from finishtypes) ft

select * 
  From products p
  join productfinishes pf on p.productid = pf.productid
  join productsizes ps on p.productid = ps.productid
  join finishtypes ft on ft.finishtypeid = pf.finishtypeid
 order by productsize

drop table finishtypes
drop table products
drop table productfinishes
drop table productsizes
1 Like
8

If you're going to normalize this, then you should truly normalize it.

Any combined measurement, such as
3 1/2" x 15"'
is not normalized. You should separate it into a numeric width and length (and height should be available as well, even though it's NULL in this case) and an accompanying measure (code representing inch, foot, meter, etc.). It's likely you'd want a "sizes" table as well, since other products may also be 3.5 x 15" or 3x12", etc.

9

agreed. that is I was asking him if there were more sizes.

10

Thank you very much! This is very helpful.

11

hi i know its been a long time

Something different
I tried
SQL using information_schema.columns

"Seniors" please your opinion and thoughts very welcome
opportunity for me to learn
:slight_smile:
:slight_smile:

drop create data 
USE tempdb 

go 

DROP TABLE product 

go 

CREATE TABLE product 
  ( 
     product VARCHAR(100), 
     size    VARCHAR(100) 
  ) 

go 

INSERT INTO product 
SELECT '70A', 
       '3" x 12"' 

INSERT INTO product 
SELECT '70A', 
       '3 1/2" x 15"' 

go 

DROP TABLE productfinish 

go 

CREATE TABLE productfinish 
  ( 
     product VARCHAR(100), 
     size    VARCHAR(100), 
     us26d   INT, 
     us26    INT, 
     us32d   INT 
  ) 

go 

INSERT INTO productfinish 
SELECT '70A', 
       '3" x 12" ', 
       10, 
       12, 
       8 

INSERT INTO productfinish 
SELECT '70A', 
       '3 1/2" x 15" ', 
       15, 
       20, 
       10 

go 

SELECT * 
FROM   product 

go 

SELECT * 
FROM   productfinish
SQL using information_schema.columns 
;WITH cte 
     AS (SELECT Row_number() 
                  OVER ( 
                    ORDER BY ordinal_position) AS rn, 
                column_name 
         FROM   information_schema.columns 
         WHERE  table_name = 'productfinish' 
                AND column_name LIKE 'US%'), 
     cte123 
     AS (SELECT us26d, 
                us26, 
                us32d 
         FROM   productfinish) 
SELECT * 
FROM   cte123, 
       cte 

go
Result

image