Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Data manipulation parent/child 2

Status
Not open for further replies.
jadams0173

jadams0173

Technical User
Feb 18, 2005
1,210
Hi all. I've used the answer in thread183-1571629 to unpivot a sql 2000 table. Now I need to take it one step further but I'm not sure where to start. I now have data that looks like this.
Code: 
RELATIONSHIP ASSY 
PARENT       011212001013T
CHILD        120989007013
PARENT       011212001013T
CHILD        120989010013
PARENT       011212001013T
CHILD        120989022013
PARENT       011212002013G
CHILD        120989007013
PARENT       011212002013G
CHILD        120989010013
PARENT       011212002013G
CHILD        120989022013


is there anyway to format the data so it is displayed with the parent and then all the child assy's without repeating PARENT for each record. This would require taking out the parent records in [red] red [/red]:

Code: 
RELATIONSHIP ASSY 
PARENT       011212001013T
CHILD        120989007013
[red]PARENT       011212001013T[/red]
CHILD        120989010013
[red]PARENT       011212001013T[/red]
CHILD        120989022013
PARENT       011212002013G
CHILD        120989007013
[red]PARENT       011212002013G[/red]
CHILD        120989010013
[red]PARENT       011212002013G[/red]
CHILD        120989022013


Here is the source query for the recordset
Code: 
SELECT * FROM (
SELECT
   RELATIONSHIP,
   ASSY =
      CASE N
      WHEN 1 THEN "Parent"
      WHEN 2 THEN "Child"
      --WHEN 3 THEN Col3
      --WHEN 4 THEN Col4
      END
FROM
   V_Parent_Child_EDS T
   CROSS JOIN (
     		SELECT 1, 'PARENT' UNION ALL SELECT 2, 'CHILD' 
   ) X (n, RELATIONSHIP)
) Z
WHERE ASSY <> ''
 
Sort by date Sort by votes
MARKROS,

Now it's time for me to say sorry for taking so long to get back to forum. It all hit at once here!

That procedure is only for sql2005 and above unless I missed something in the blog.

Here is the data that I'm working with:
Code: 
RECORD_ID            PARENT                    CHILD                     SAME 
-------------------- ------------------------- ------------------------- ---- 
64111499             010505001013A             161635001013A             N
507                  010505001013A             161638001013A             N
64111532             010505001013B             161635001013A             N
509                  010505001013B             161638001013A             N
64111469             010505001013C             161635001013A             N
511                  010505001013C             161638001013A             N
64345081             010763001013M             120989006013              N
64345082             010763001013M             120989007013              N
71099819             010763001013N             120989006013              N
71099820             010763001013N             120989007013              N
64111553             011203001013L             227846001013A             N
64345749             011203001013N             227846001013A             N
71099823             011203001013P             227846001013A             N
64308943             011212001013R             120989007013              N
64308944             011212001013R             120989010013              N
64308945             011212001013R             120989022013              N
64308959             011212001013S             120989007013              N
64308960             011212001013S             120989010013              N
64308961             011212001013S             120989022013              N
64111534             011212001013T             120989007013              N
64111535             011212001013T             120989010013              N
64111536             011212001013T             120989022013              N
64111472             011212002013G             120989007013              N
64111473             011212002013G             120989010013              N
64111474             011212002013G             120989022013              N
64111583             011212002013H             120989007013              N
64111584             011212002013H             120989010013              N
64111585             011212002013H             120989022013              N
64111537             011212002013J             120989007013              N
64111538             011212002013J             120989010013              N
64111539             011212002013J             120989022013              N
64111475             011212003013F             120989007013              N
64111476             011212003013F             120989010013              N
64111477             011212003013F             120989022013              N
64343179             011212003013G             120989007013              N
64343180             011212003013G             120989010013              N
64343181             011212003013G             120989022013              N
64111586             011212003013H             120989007013              N
64111587             011212003013H             120989010013              N
64111588             011212003013H             120989022013              N
64111540             011212003013J             120989007013              N
64111541             011212003013J             120989010013              N
64111542             011212003013J             120989022013              N
64111557             011212003013K             120989007013              N
64111558             011212003013K             120989010013              N
64111559             011212003013K             120989022013              N
71105636             011212003013M             120989007013              N
71105637             011212003013M             120989010013              N
71105638             011212003013M             120989022013              N
71097283             011212003013N             120989007013              N
71097284             011212003013N             120989010013              N
71097285             011212003013N             120989022013              N
64111560             011647001013G             120989011013A             N
71097916             011647001013J             120989011013A             N
64308966             011977001013Q             120989021013              N
64111501             011977001013R             120989021013              N
64111546             011977001013S             120989021013              N
64111521             011977002013P             120989021013              N
64111502             011977002013Q             120989021013              N
71101139             012845001013D             313495004013C             N

desired output.

Code: 
 RELATIONSHIP    ASSY                      

PARENT       010505001013A
CHILD        161635001013A
CHILD        161638001013A
PARENT       010505001013B
CHILD        161635001013A
CHILD        161638001013A
PARENT       010505001013C
CHILD        161635001013A
CHILD        161638001013A
PARENT       010763001013M
CHILD        120989006013
CHILD        120989007013
PARENT       010763001013N
CHILD        120989006013
CHILD        120989007013
PARENT       011212001013R
CHILD        120989007013
CHILD        120989010013
CHILD        120989022013
PARENT       011212001013S
CHILD        120989007013
CHILD        120989010013
CHILD        120989022013
 
Upvote 0 Downvote
I don't know what to say :( I use SQL Server Express 2008, I tried few times to import file (I saved it as results.txt), but something I'm not doing right in the Import wizard, because I'm getting

Error 0xc0202004: Data Flow Task 1: The number of columns is incorrect.
(SQL Server Import and Export Wizard)

Error 0xc0202025: Data Flow Task 1: Cannot create an OLE DB accessor. Verify that the column metadata is valid.
(SQL Server Import and Export Wizard)

Error 0xc004701a: Data Flow Task 1: component "Destination - Result" (26) failed the pre-execute phase and returned error code 0xC0202025.
(SQL Server Import and Export Wizard)


--------------
I also tried using Excel and got other errors. I give up for now :(((
 
Upvote 0 Downvote
Quite frustrating, actually :(

I think I would be much better installing Developer's edition of SQL Server at home's PC, but I'm dreading a process of re-install.
 
Upvote 0 Downvote
I dread that same process when i have to do it.

I've tried to make it a little more friendly for those who want to assist.

Maybe this will make it a little more inviting for you! :)


Code: 
Declare @TestTable TABLE
(
    record_id bigint,
    parent  VarChar(25),
    Child   VarChar(25)
)
set nocount on

Insert into @TestTable values(64111532,'010505001013B','161635001013A')
Insert into @TestTable values(509,'010505001013B','161638001013A')
Insert into @TestTable values(64111469,'010505001013C','161635001013A')
Insert into @TestTable values(511,'010505001013C','161638001013A')
Insert into @TestTable values(71098156,'1081074001411C','1081056411D')
Insert into @TestTable values(71098157,'1081074001411C', '1081057411C') 
Insert into @TestTable values(71098158,'1081074001411C', '1081062411E')
Insert into @TestTable values(71098160, '1081074001411C','1081080411A')
Insert into @TestTable values(71098161, '1081074001411C', '1081106001411B')
Insert into @TestTable values(64111499 ,'010505001013A','161635001013A')
Insert into @TestTable values(507 ,'010505001013A','161638001013A')

Select * from @TestTable

/*
output would be in this fashion

Relationship  Assy
Parent         010505001013B
Child          161635001013A
Child          161638001013A
Parent         010505001013C
Child          161635001013A
Child          161638001013A
Parent         1081074001411C
Child          1081056411D
Child          1081057411C
Child          1081062411E
Child          1081080411A
Child          1081106001411B
Parent         010505001013A
Child          161635001013A
Child          161638001013A
*/
 
Upvote 0 Downvote
try this:

Code: 
SELECT Z.Relationship,
       Z.Assy
FROM   (
       SELECT RELATIONSHIP,
              ASSY = CASE N
                     WHEN 1 THEN "Parent"
                     WHEN 2 THEN "Child"
                     END,
	          Record_ID
       FROM V_Parent_Child_EDS T
            CROSS JOIN (
             SELECT 1, 'PARENT' UNION ALL SELECT 2, 'CHILD'
            ) X (n, RELATIONSHIP)
       ) Z
       Left Join (
         Select Min(Record_id) As MinRecordId, Parent
         From   V_Parent_Child_EDS
         Group By Parent
         ) As A
           On Z.Assy = A.Parent
           And Z.Record_id = A.MinRecordId
WHERE ASSY <> '' 
      And A.parent is null



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Almost...but not quite.


here is what I got when I ran your query. All the Parents repeat. I was hoping for one Parent and then all the children under that parent. It also almost looks like something is duplicating. I'm going to verify that my data doesn't have duplicates in it.

Code: 
PARENT       1081074001411C
CHILD        1081030411A
CHILD        1081039411C
PARENT       1081074001411C
CHILD        1081050411A
PARENT       1081074001411C
CHILD        1081054411D
PARENT       1081074001411C
CHILD        1081056411D
PARENT       1081074001411C
CHILD        1081057411C
PARENT       1081074001411C
CHILD        1081062411E
PARENT       1081074001411C
CHILD        1081080411A
PARENT       1081074001411C
CHILD        1081106001411B

Code: 
Parent         1081074001411C
Child          1081056411D
Child          1081057411C
Child          1081062411E
Child          1081080411A
Child          1081106001411B
 
Upvote 0 Downvote
Is it possible that your Record_ID is not unique for each parent ?
 
Upvote 0 Downvote
Hey again markros. The record ID is unique.

I've scrapped the set base solution and have given in to using a cursor and recursion for this situation. This approach has give me the data I was looking for although I had to start from scratch...
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

peac3
  • Locked
  • Question
convert hierarchy structure to parent children pair please... 1
2
Replies
22
Views
662
mikrom
mikrom
coorsman
  • Locked
  • Question
Column Data Type. 2
Replies
9
Views
332
Chris Miller
Chris Miller
peac3
  • Locked
  • Question
fill the gap of hierarchy data
Replies
11
Views
400
Chris Miller
Chris Miller
Andrzejek
  • Locked
  • Question
Data types and sizes
Replies
18
Views
294
gmmastros
gmmastros
gussy81
  • Locked
  • Question
Decimal Datatype with negative value
Replies
5
Views
648
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top