creating a FLAT table from a PARENT-CHILD structure?

[alfredjp]

having a PARENT-CHILD structure table as follows (product_level can go as high as 6)

 product_id int
 product_parent_id int
 product_level int
 product_name varchar
 <some data> <some type>

i would like to write a script (the more efficient, the better) to create a flat version of it that would look as below:

 name1, id1, parent_id1,... nameX, idX, parent_idX .... name6, id6, parent_id6

with some columns having NULL values if the corresponding leaf is non-existent

well... i have written a code too, but i find it too inefficient, and only usable for this case...

if anyone could at least provide me with an algorithm, id really appreciate it

thanks!

 

[TheGreenOne]

If I understand correctlly you have parent records and children in the same table, and the product_level would contain a number between 1 -6. You want a 7 column result with the first column the parent, and columns 2-7 the children. I can do that...

SELECT
parent_id = prod1.product_id,
parent_name = prod1.product_name,
child1_id = (
select product_id
from products prod2
where prod2.product_parent_id = prod1.product_id and prod2.product_level = 1),
child1_name = (
select product_name
from products prod2
where prod2.product_parent_id = prod1.product_id and prod2.product_level = 1),
child2_id = (
select product_id
from products prod2
where prod2.product_parent_id = prod1.product_id and prod2.product_level = 2),
child2_name = (
select product_name
from products prod2
where prod2.product_parent_id = prod1.product_id and prod2.product_level = 2),
child3_id = (
select product_id
from products prod2
where prod2.product_parent_id = prod1.product_id and prod2.product_level = 3),
child3_name = (
select product_name
from products prod2
where prod2.product_parent_id = prod1.product_id and prod2.product_level = 3),
child4_id = (
select product_id
from products prod2
where prod2.product_parent_id = prod1.product_id and prod2.product_level = 4),
child4_name = (
select product_name
from products prod2
where prod2.product_parent_id = prod1.product_id and prod2.product_level = 4),
child5_id = (
select product_id
from products prod2
where prod2.product_parent_id = prod1.product_id and prod2.product_level = 5),
child5_name = (
select product_name
from products prod2
where prod2.product_parent_id = prod1.product_id and prod2.product_level = 5),
child6_id = (
select product_id
from products prod2
where prod2.product_parent_id = prod1.product_id and prod2.product_level = 6),
child6_name = (
select product_name
from products prod2
where prod2.product_parent_id = prod1.product_id and prod2.product_level = 6)

FROM products prod1

To avoid confusion I have assigned an alias of prod1 to the table name in the outer select, and prod2 for the table name of the inner selects. I don't know how effecient it will be, let me know.

This method requires that there is only one record for each parent for product_level = 1. It would be too easy to have multiple product_level 1 records for a single parent. You should add 'Top 1' to the inner select statements to ensure that you only get 1 record. like this:
(select top 1 product_id
from products prod2
where prod2.product_parent_id = prod1.product_id and prod2.product_level = 1
order by product_id)

 

[alfredjp]

hhhhhmmm...
you are using multiple joins in a single select query...
because of lack of experience, im not really sure if having ALL your queries inside ONE query is better than having ALL your queries execute consecutively

anyway, my own version goes like this (wherein i created a temporary table <--- not the temporary table defined with a #)

IF EXISTS (SELECT name 
     FROM   sysobjects 
     WHERE  name = N'FLAT_PRODUCT_MASTER'
     AND    type = 'U')
    DROP TABLE FLAT_PRODUCT_MASTER
GO

CREATE TABLE [dbo].[FLAT_PRODUCT_MASTER] (
      ----
      ----
GO

INSERT INTO FLAT_PRODUCT_MASTER (
  PRODUCT_CODE6,
  SHORT_PRODUCT_CODE6,
  PRODUCT_NAME6,
  PRODUCT_ID6,
  PRODUCT_PARENT_ID6
)
SELECT
  PRODUCT_CODE AS PRODUCT_CODE6,
  SHORT_PRODUCT_CODE AS SHORT_PRODUCT_CODE6,
  PRODUCT_NAME_KANJI AS PRODUCT_NAME6,
  PRODUCT_ID AS PRODUCT_ID6,
  PRODUCT_PARENT_ID AS PRODUCT_PARENT_ID6
FROM
  PRODUCT
WHERE
  PRODUCT_LEVEL = 6
  AND PRODUCT_ID <> 1
ORDER BY
  1

UPDATE FLAT_PRODUCT_MASTER
SET
  PRODUCT_CODE5 = Src.PRODUCT_CODE,
  SHORT_PRODUCT_CODE5 = Src.SHORT_PRODUCT_CODE,
  PRODUCT_NAME5 = Src.PRODUCT_NAME_KANJI,
  PRODUCT_ID5 = Src.PRODUCT_ID,
  PRODUCT_PARENT_ID5 = Src.PRODUCT_PARENT_ID
FROM
  PRODUCT AS Src
  INNER JOIN FLAT_PRODUCT_MASTER AS Dst
    ON Src.PRODUCT_ID = Dst.PRODUCT_PARENT_ID6
WHERE
  PRODUCT_LEVEL = 5
  AND PRODUCT_ID <> 1

UPDATE FLAT_PRODUCT_MASTER
SET
  PRODUCT_CODE4 = Src.PRODUCT_CODE,
  SHORT_PRODUCT_CODE4 = Src.SHORT_PRODUCT_CODE,
  PRODUCT_NAME4 = Src.PRODUCT_NAME_KANJI,
  PRODUCT_ID4 = Src.PRODUCT_ID,
  PRODUCT_PARENT_ID4 = Src.PRODUCT_PARENT_ID
FROM
  PRODUCT AS Src
  INNER JOIN FLAT_PRODUCT_MASTER AS Dst
    ON Src.PRODUCT_ID = Dst.PRODUCT_PARENT_ID5
WHERE
  PRODUCT_LEVEL = 4
  AND PRODUCT_ID <> 1

UPDATE FLAT_PRODUCT_MASTER
SET
  PRODUCT_CODE3 = Src.PRODUCT_CODE,
  SHORT_PRODUCT_CODE3 = Src.SHORT_PRODUCT_CODE,
  PRODUCT_NAME3 = Src.PRODUCT_NAME_KANJI,
  PRODUCT_ID3 = Src.PRODUCT_ID,
  PRODUCT_PARENT_ID3 = Src.PRODUCT_PARENT_ID
FROM
  PRODUCT AS Src
  INNER JOIN FLAT_PRODUCT_MASTER AS Dst
    ON Src.PRODUCT_ID = Dst.PRODUCT_PARENT_ID4
WHERE
  PRODUCT_LEVEL = 3
  AND PRODUCT_ID <> 1

UPDATE FLAT_PRODUCT_MASTER
SET
  PRODUCT_CODE2 = Src.PRODUCT_CODE,
  SHORT_PRODUCT_CODE2 = Src.SHORT_PRODUCT_CODE,
  PRODUCT_NAME2 = Src.PRODUCT_NAME_KANJI,
  PRODUCT_ID2 = Src.PRODUCT_ID,
  PRODUCT_PARENT_ID2 = Src.PRODUCT_PARENT_ID
FROM
  PRODUCT AS Src
  INNER JOIN FLAT_PRODUCT_MASTER AS Dst
    ON Src.PRODUCT_ID = Dst.PRODUCT_PARENT_ID3
WHERE
  PRODUCT_LEVEL = 2
  AND PRODUCT_ID <> 1

UPDATE FLAT_PRODUCT_MASTER
SET
  PRODUCT_CODE1 = Src.PRODUCT_CODE,
  SHORT_PRODUCT_CODE1 = Src.SHORT_PRODUCT_CODE,
  PRODUCT_NAME1 = Src.PRODUCT_NAME_KANJI,
  PRODUCT_ID1 = Src.PRODUCT_ID,
  PRODUCT_PARENT_ID1 = Src.PRODUCT_PARENT_ID
FROM
  PRODUCT AS Src
  INNER JOIN FLAT_PRODUCT_MASTER AS Dst
    ON Src.PRODUCT_ID = Dst.PRODUCT_PARENT_ID2
WHERE
  PRODUCT_LEVEL = 1
  AND PRODUCT_ID <> 1

 

[alfredjp]

TO: TheGreenOne (Programmer)

btw, thanks for providing me at least a new way of accomplishing the same thing