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
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 <> '' group by Relationship, Assy
 
Upvote 0 Downvote
Thanks markros. Your solution provides me with this

Code: 
RELATIONSHIP ASSY                      
------------ ------------------------- 
CHILD        004027050001035
CHILD        00402983001035A
CHILD        0040340600035A
CHILD        012098001013M
CHILD        012098001013N
CHILD        012335001013M
HILD        Y75M810459A
CHILD        Y75M845459A
PARENT       011203001013N
PARENT       011212001013R
PARENT       011212002013G
PARENT       011212002013H
PARENT       011212002013J
PARENT       011212003013F
PARENT       011977002013Q
PARENT       012845001013D
PARENT       05000082A
PARENT       05000082B
PARENT       0670652500035A
CHILD        9B8221120A
CHILD        A1172211SM375B
CHILD        A1172239SM375B
CHILD        A1172245SM375A
CHILD        A1172245SM375B

I just need to remove the red items above without restruction the order of the data.
 
Upvote 0 Downvote
Yes, I understand. Do you know how exactly the original set was ordered? In other words, can we somehow apply the ordering to get the same set?

We can, of course, create a temp table with identity column, append the original result, remove duplicate parent records.

 
Upvote 0 Downvote
BTW, should your order by ASSY, Relationship ? I was trying to invent more complex solution, but perhaps just by adding ORDER BY ASSY, Relationship will give you the desired result ?
 
Upvote 0 Downvote
Markros,
There is an identity column in the view. I'm trying to add it but keep getting,

Server: Msg 8159, Level 16, State 1, Line 1
'X' has fewer columns than were specified in the column list.
column name in the view is reocrd_id
Code: 
SELECT * FROM (
SELECT
	RECORD_ID,
   RELATIONSHIP,
   ASSY =
      CASE N
      WHEN 1 THEN "Parent"
      WHEN 2 THEN "Child"
      WHEN 3 THEN "RECORD_ID"
      --WHEN 4 THEN Col4
      END
FROM
   V_Parent_Child_EDS T
   CROSS JOIN (
             SELECT 1, 'PARENT' UNION ALL SELECT 2, 'CHILD' UNION ALL SELECT 3, 'RECORD_ID'
   ) X (N, RELATIONSHIP)
) Z
 
Upvote 0 Downvote
It's a different method for naming your columns.

Ex:

Code: 
Select *
From (
	Select 1, 'Blue'
	Union All
	Select 2, 'Blue'
	) As Blah

Since both columns are hard coded values, when you run this in a query window you will get "[!]No column was specified for column 1 of 'Blah'[/!]"

Now run this:

Code: 
Select *
From (
	Select 1, 'Blue'
	Union All
	Select 2, 'Blue'
	) As Blah [!](Number, Color)[/!]

Now the columns do have a name. Specifically, the first column is named "Number" and the second column is named "Color"

You could also have written it this way.

Code: 
Select *
From (
	Select 1 [!]As Number[!], 'Blue' [!]As Color[/!]
	Union All
	Select 2, 'Blue'
	) As Blah

Note that since this is a union query, only the first query needs aliasing.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
I guessed about it, but it doesn't explain the error above the OP is getting.

Thanks for the explanation, BTW.
 
Upvote 0 Downvote
If I do it this way

Code: 
SELECT * FROM (
SELECT
	RECORD_ID,
   RELATIONSHIP,
   ASSY =
      CASE N
      WHEN 1 THEN "Parent"
      WHEN 2 THEN "Child"
      --WHEN 3 THEN "RECORD_ID"
      --WHEN 4 THEN Col4
      END
FROM
   V_Parent_Child_EDS T
   CROSS JOIN (
             SELECT 1, 'PARENT' UNION ALL SELECT 2, 'CHILD' --UNION ALL SELECT 3, 'RECORD_ID'
   ) X (N,RELATIONSHIP)
) Z
WHERE ASSY <> ''

I get these results
Code: 
RECORD_ID            RELATIONSHIP ASSY      
64308959             PARENT       011212001013S
64308959             CHILD        120989007013
64308960             PARENT       011212001013S
64308960             CHILD        120989010013
64308961             PARENT       011212001013S
64308961             CHILD        120989022013
64111534             PARENT       011212001013T
64111534             CHILD        120989007013
64111535             PARENT       011212001013T
64111535             CHILD        120989010013
64111536             PARENT       011212001013T
64111536             CHILD        120989022013
64111472             PARENT       011212002013G
64111472             CHILD        120989007013
64111473             PARENT       011212002013G
64111473             CHILD        120989010013
64111474             PARENT       011212002013G
64111474             CHILD        120989022013
 
Upvote 0 Downvote
Try
Code: 
SELECT Record_ID, Relationship, Assy FROM (
SELECT
    RECORD_ID,
   RELATIONSHIP,
   ASSY =
      CASE N
      WHEN 1 THEN "Parent"
      WHEN 2 THEN "Child"
      --WHEN 3 THEN "RECORD_ID"
      --WHEN 4 THEN Col4
      END,
   case N when 1 then Parent else NewID() end as ExtraGroup  
FROM
   V_Parent_Child_EDS T
   CROSS JOIN (
             SELECT 1, 'PARENT' UNION ALL SELECT 2, 'CHILD' --UNION ALL SELECT 3, 'RECORD_ID'
   ) X (N,RELATIONSHIP)
) Z
WHERE ASSY <> '' group by ExtraGroup, ASSY order by Record_ID
 
Upvote 0 Downvote
Hey markros


I get these errors. not sure which group by they go in
Code: 
Server: Msg 8120, Level 16, State 1, Line 21
Column 'Z.Record_ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 21
Column 'Z.Relationship' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
 
Upvote 0 Downvote
Now I have;
Code: 
SELECT Record_ID, Relationship, Assy FROM (
SELECT
    RECORD_ID,
   RELATIONSHIP,
   ASSY =
      CASE N
      WHEN 1 THEN "Parent"
      WHEN 2 THEN "Child"
      --WHEN 3 THEN "RECORD_ID"
      --WHEN 4 THEN Col4
      END,
   case N when 1 then Parent else NewID() end as ExtraGroup  
FROM
   V_Parent_Child_EDS T
   CROSS JOIN (
             SELECT 1, 'PARENT' UNION ALL SELECT 2, 'CHILD' --UNION ALL SELECT 3, 'RECORD_ID'
   ) X (N,RELATIONSHIP)
) Z
WHERE ASSY <> '' 
group by ExtraGroup, ASSY , Z.Record_ID, Z.Relationship
order by Record_ID

and I get this error. I think it has something to do with
Code: 
case N when 1 then Parent else NewID() end as ExtraGroup

trying to put the new id into a varchar field?

 
Upvote 0 Downvote
Oops - I'm sorry - I see several mistakes now in the code I posted - and sorry for not replying earlier - too many forums opened <g>

Code: 
SELECT min(Record_ID) as Record_ID, Relationship, Assy FROM (
SELECT
    RECORD_ID,
   RELATIONSHIP,
   ASSY =
      CASE N
      WHEN 1 THEN "Parent"
      WHEN 2 THEN "Child"
      --WHEN 3 THEN "RECORD_ID"
      --WHEN 4 THEN Col4
      END,
   cast(case N when 1 then "PARENT" else NewID() end as varchar(32)) as ExtraGroup  
FROM
   V_Parent_Child_EDS T
   CROSS JOIN (
             SELECT 1, 'PARENT' UNION ALL SELECT 2, 'CHILD' --UNION ALL SELECT 3, 'RECORD_ID'
   ) X (N,RELATIONSHIP)
) Z
WHERE ASSY <> ''
group by ExtraGroup, ASSY , Relationship
order by Record_ID

try this - sorry, not tested again.
 
Upvote 0 Downvote
Marrkros,

Oops - I'm sorry - I see several mistakes now in the code I posted - and sorry for not replying earlier - too many forums opened <g>
Click to expand...

I'm GREATFUL for your help. No need to apologize for taking some time to reply and an error here or there. Again, many thanks. :) [thumbsup]

However, I still get this error

Code: 
Server: Msg 8169, Level 16, State 2, Line 21
Syntax error converting from a character string to uniqueidentifier.
 
Upvote 0 Downvote
case N when 1 then Parent else NewID() end as ExtraGroup
Click to expand...

Syntax error converting from a character string to uniqueidentifier.
Click to expand...

The simplest way to reproduce this error message is...

Code: 
Select Case When 1=1 
            Then 'Hello World' 
            Else NewId() 
            End As Blah

When SQL Server parses your query, it attempts to convert the results of each branch of execution in to the same data type. SQL uses data type precedence to do this. So, instead of SQL converting a uniqueidentifier to a string, it converts the string to a unique identifier. Hello World is NOT a valid unique identifier, so you get the error.

Notice in the example I post above... It's impossible to actually return the unique identifier because of the "when 1=1" part, but that does not matter.

Read more about this here:


To fix the problem, simply convert the unique identifier to a string, and problem solved. Like this:

Code: 
Select Case When 1=1 
            Then 'Hello World' 
            Else [!]Convert(VarChar(50), [/!]NewId()[!])[/!]
            End As Blah

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Thanks, George, for correcting this error - I guess I to attend need 101 class for SQL Server.
 
Upvote 0 Downvote
Hey George,

That fixed that error however my result set is still the same as previous.

The code now looks like
Code: 
SELECT min(Record_ID) as Record_ID, Relationship, Assy FROM (
SELECT
    RECORD_ID,
   RELATIONSHIP,
   ASSY =
      CASE N
      WHEN 1 THEN "Parent"
      WHEN 2 THEN "Child"
      --WHEN 3 THEN "RECORD_ID"
      --WHEN 4 THEN Col4
      END,
   case N 
	when 1 then "PARENT" 
	else convert(varchar(50),NewID() )
	end  as ExtraGroup  
FROM
   V_Parent_Child_EDS T
   CROSS JOIN (
             SELECT 1, 'PARENT' UNION ALL SELECT 2, 'CHILD' --UNION ALL SELECT 3, 'RECORD_ID'
   ) X (N,RELATIONSHIP)
) Z
WHERE ASSY <> ''
group by ExtraGroup, ASSY , Relationship
order by Record_ID

the result set still looks like the above post.
 
Upvote 0 Downvote
oops...

the data is slightly different but still not right

Code: 
Record_ID            Relationship Assy                      
-------------------- ------------ ------------------------- 
507                  PARENT       010505001013A
507                  CHILD        161638001013A
509                  PARENT       010505001013B
509                  CHILD        161638001013A
511                  PARENT       010505001013C
511                  CHILD        161638001013A
2695                 CHILD        153860001013A
2695                 PARENT       02874013F
2697                 PARENT       02874013G
2697                 CHILD        153860001013A
2699                 PARENT       02874013H
2699                 CHILD        153860001013A
2705                 CHILD        153860001013A
2705                 PARENT       02879013E
2707                 PARENT       02879013F
2707                 CHILD        153860001013A
2709                 PARENT       02879013G
2709                 CHILD        153860001013A
4675                 PARENT       0670559600035A
4675                 CHILD        0040448700035A
4676                 CHILD        0040448800035A
4677                 CHILD        0610294900035A
4678                 CHILD        0640191600035A
11644                CHILD        700131006346
11644                PARENT       109366146346
28918                PARENT       216969-A016F
28918                CHILD        316409-B016F
28920                PARENT       216969-A016G
28920                CHILD        316409-B016G
32991                PARENT       270036437106A
32991                CHILD        270031479106A
32993                CHILD        270031479106A
32993                PARENT       270036437106B
32995                PARENT       270036437106C
32995                CHILD        270031479106A
33633                CHILD        270039324106A
33633                PARENT       280013160106A
33635                PARENT       280013160106B
33635                CHILD        270039324106A
33646                CHILD        270049224106B
33646                PARENT       280025420106C
37126                PARENT       314153B21013B
37126                CHILD        302544001013A
37664                PARENT       316239-A016D
37664                CHILD        316210-A016D
37814                PARENT       316412-B016F
37814                CHILD        316409-B016F
37816                PARENT       316412-B016G
37816                CHILD        316409-B016G
37818                PARENT       316412-B016H
37818                CHILD        316409-B016H
37849                CHILD        316411-A016B
37849                PARENT       316414-A016B
38159                PARENT       317236-A016C
38159                CHILD        316410-A016C
38245                PARENT       317371-A016C
38245                CHILD        316210-A016D
38295                PARENT       317469-A016V
38295                CHILD        318614-A016Q
38303                CHILD        318614-A016Q
38303                PARENT       317473-A016U
38494                PARENT       318048-A016H
38494                CHILD        318052-A016W
38495                CHILD        318058-A016AF
38496                CHILD        318059-A016H
38505                CHILD        318058-A016AF
38505                PARENT       318052-A016W
38506                CHILD        318059-A016H
38606                PARENT       318131-A016AT
38606                CHILD        317856-A016AV
38608                PARENT       318131-A016AU
38608                CHILD        317856-A016AW
38610                PARENT       318131-A016AV
38610                CHILD        317856-A016AX
38612                PARENT       318131-A016AW
38612                CHILD        317856-A016AY
38614                CHILD        317856-A016AZ
38614                PARENT       318131-A016AX
38644                PARENT       318141-A016AR
38644                CHILD        317859-A016AV
38648                CHILD        317859-A016AX
38648                PARENT       318141-A016AT
38650                PARENT       318141-A016AU
38650                CHILD        317859-A016AY
 
Upvote 0 Downvote
Can you post some sample data from the original table? And the expected results?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
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