SQL Select - vertical to horizontal alignment

I have the following table and want to display he data in a horizontal alignment where I would have a separate column for each object Type:

Data:

ID          Name        Object Type   Parent ID
 1          Test              Level 1              
2          Sample          Level 2         1
3          .........              Level 3         2
4.        ...........            Level 3          2
5        ............            Level 2          1

I would like this data to be formatted like this:

Level 1 ID    Level 1 Name     Level 2 ID   Level2 Name   Level 3 ID   Level3 Name
    1                  Test                            2          Sample                3               ...............
    1                  Test                            2          Sample                4              ...............
    1                  Test                            5          Sample
tmajor99Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tmajor99Author Commented:
This did not help.  

I have the following table and want to display he data in a horizontal alignment where I would have a separate column for each object Type:

 Data:

 ID          Name        Object Type   Parent ID
  1          Test              Level 1              
 2          Sample          Level 2         1
 3          .........              Level 3         2
 4.        ...........            Level 3          2
 5        ............            Level 2          1

 I would like this data to be formatted like this:

 Level 1 ID    Level 1 Name     Level 2 ID   Level2 Name   Level 3 ID   Level3 Name
     1                  Test                            2          Sample                3               ...............
     1                  Test                            2          Sample                4              ...............
     1                  Test                            5          Sample
0
Vadim RappCommented:
create table ttt(id int,name varchar(50), type varchar(50), parentid int)
insert into ttt(id,name,type,parentid) select 1,'test','level1',null
insert into ttt(id,name,type,parentid) select 2,'sample','level2',1
insert into ttt(id,name,type,parentid) select 3,null,'level3',2
insert into ttt(id,name,type,parentid) select 4,null,'level3',2
insert into ttt(id,name,type,parentid) select 5,null,'level2',1

select l1.id,l1.name,l2.id,l2.name,l3.id,l3.name,l4.id,l4.name
from ttt l1
left join ttt l2 on l2.parentid=l1.id
left join ttt l3 on l3.parentid=l2.id
left join ttt l4 on l4.parentid=l3.id

Open in new window

0
tmajor99Author Commented:
Is there a better way to do this?  Like by using nested queries?
"Select 

SELECT 
(  Select ([STEP-DEV-L1-4].[<ID>]) FROM [STEP-DEV-L1-4] where [STEP-DEV-L1-4].[<Object Type Name>] = 'Level 1' ) as 'L1-ID',
(  Select ([STEP-DEV-L1-4].[<Name>]) FROM [STEP-DEV-L1-4] where [STEP-DEV-L1-4].[<Object Type Name>] = 'Level 1' ) as 'L1-Name',
(  Select ([STEP-DEV-L1-4].[<Parent ID>]) FROM [STEP-DEV-L1-4] where [STEP-DEV-L1-4].[<Object Type Name>] = 'Level 1' ) as 'L1-Parent',.......

FROM [STEP-DEV-L1-4]"

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Bob LearnedCommented:
You should be able to achieve that with a Common Table Expression (CTE):

Common Table Expressions(CTE) in SQL SERVER 2008
http://www.codeproject.com/Articles/265371/Common-Table-Expressions-CTE-in-SQL-SERVER
0
dsackerContract ERP Admin/ConsultantCommented:
Hi tmajor99. Please post a complete representation of your data,  enough rows that it takes to make the final results you'd like to see. Please don't use dots (...........).

A question: Is the Parent ID involved in what you'd like to see? Or is that just the ID field we're seeing in the final results?

Thanks.
0
tmajor99Author Commented:
ID          Name        Object Type   Parent ID
  1          Toys              Level 1              
  2           Bikes             Level 2         1
  3           Pedal             Level 3         2
  4.          Motor            Level 3          2
  5           Dolls              Level 2          1
  9           Electronics    Level 2          1

In a tree view it would look like this:
- Toys
     - Bikes
           - Pedal
           - Motor
     - Dolls  
- Electronics  

  I would like this data to be formatted like this:

  Level 1 ID    Level 1 Name     Level 2 ID   Level2 Name   Level 3 ID   Level3 Name
      1                  Toys                           2          Bikes              3               Pedal
      1                  Toys                           2          Bikes              4               Motor
      1                  Toys                           5          Dolls              (no level 3)
      9                  Electronics                (no level 2 or 3)
0
dsackerContract ERP Admin/ConsultantCommented:
Since 9 has a parent of 1, wouldn't your data be like this?
Level 1 ID    Level 1 Name     Level 2 ID   Level2 Name   Level 3 ID   Level3 Name 
    1         Toys                 2        Bikes             3           Pedal
    1         Toys                 2        Bikes             4           Motor
    1         Toys                 5        Dolls            NULL         NULL
    1         Toys                 9        Electronics      NULL         NULL

Open in new window

0
dsackerContract ERP Admin/ConsultantCommented:
If my above question is yes, and the data I presented above is correct, here is a working solution using a temp table called @Table:
DECLARE @Table TABLE (
    ID        tinyint     NOT NULL,
    Name      varchar(12) NOT NULL,
    ObjType   varchar(12) NOT NULL,
    ParentID  tinyint )

INSERT INTO @Table VALUES (1, 'Toys', 'Level 1', NULL)
INSERT INTO @Table VALUES (2, 'Bikes', 'Level 2', 1)
INSERT INTO @Table VALUES (3, 'Pedal', 'Level 3', 2)
INSERT INTO @Table VALUES (4, 'Motor', 'Level 3', 2)
INSERT INTO @Table VALUES (5, 'Dolls', 'Level 2', 1)
INSERT INTO @Table VALUES (9, 'Electronics', 'Level 2', 1)

SELECT t1.ID    AS [Level 1 ID],
       t1.Name  AS [Level 1 Name],
       t2.ID    AS [Level 2 ID],
       t2.Name  AS [Level 2 Name],
       t3.ID    AS [Level 3 ID],
       t3.Name  AS [Level 3 Name]
FROM   @Table t1
LEFT JOIN @Table t2 ON t2.ParentID = t1.ID
LEFT JOIN @Table t3 ON t3.ParentID = t2.ID
WHERE  t1.ID = (SELECT MIN(ParentID) FROM @Table)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dsackerContract ERP Admin/ConsultantCommented:
Is this some home assignment that requires nested queries, recursive queries or what?
0
tmajor99Author Commented:
Thanks but this did not return any rows.  And yes your assumptions above are correct.
select 
l1.[<ID>] as 'L1-ID',l1.[<Name>] as 'L1-Name',l1.[<Parent ID>] as 'L1-Parent', l1.[<Object Type Name>] as 'L1-Object', l1.[GPH-Component-ID] as 'L1-Component',
l2.[<ID>] as 'L2-ID',l2.[<Name>] as 'L2-Name',l2.[<Parent ID>] as 'L2-Parent', l2.[<Object Type Name>] as 'L2-Object', l2.[GPH-Component-ID] as 'L2-Component',
l3.[<ID>] as 'L3-ID',l3.[<Name>] as 'L3-Name',l3.[<Parent ID>] as 'L3-Parent', l3.[<Object Type Name>] as 'L3-Object', l3.[GPH-Component-ID] as 'L3-Component',
from [STEP-DEV-L1-4]  l1
left join [STEP-DEV-L1-4] l2 on l2.[<Parent ID>]=l1.[<ID>]
left join [STEP-DEV-L1-4] l3 on l3.[<Parent ID>]=l2.[<ID>]
WHERE l1.[<ID>] = (SELECT MIN([<Parent ID>]) FROM [STEP-DEV-L1-4])

Open in new window

0
dsackerContract ERP Admin/ConsultantCommented:
My @Table works according to the data you gave me. This is an assignment of yours, but there's certainly nothing wrong with your seeking every resource you can, as long as you don't skim past gaining a personal understanding. Yeah, I'm older, I've taught, and I know the ropes. :)

Problem is, though, we've been doing some guess work based on incomplete information. To really help you, it's important you help me and answer concisely what I'm asking below. If this gets too much or you have already done enough to your satisfaction, I understand if the effort gets to encumbered and you decide to call it done. :)

1.

Does this assignment require nested queries or recursive queries? Answer one or the other or both only if you're sure. It's important to know which of the two your instructor requires or if he/she wants both.

2.

I find it interesting that your fields names have angle brackets in them, i.e., [<ID>]. That is usually not normal, and you'll never find it in a work environment (I started coding SQL in 1982). That said, I'd like to see the CREATE TABLE layout, if possible, i.e., your table name, your field names, everything.

3.

And finally, is the data you gave me in your post #40768691 all the data? I mean, is there more than those records?

4.

Is my correction of how the final data should look agreeable to you, as I posted in post #40768707?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.