restructure table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

restructure table

Author

Topic

musclebreast
Yak Posting Veteran

77 Posts

Posted - 2014-12-17 : 08:59:48

Hi,

I have the following table:


 dataid  	 parentid  	 subtype  	 name 
 134438  	 129574  	 144  	 Configuration Settings2.docx 
 135480  	 129244  	 2  	 Configuration Settings.docx 
 137652  	 129244  	 2  	 Configuration Settings2.docx

As you can see, some have the same name. I want all data of one name in one row, that the result is the following:



 dataid  	 parentid  	 subtype  	 name                            
 134438  	 129574  	 144  	 Configuration Settings2.docx    137652  	 129244  	 2  	 Configuration Settings2.docx 
     

 135480  	 129244  	 2  	 Configuration Settings.docx

I there a wa to do such things?

Best regards,

Lara

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-12-17 : 09:15:47

You can certainly do something like what you are proposing, but I would strongly advise against doing that. The way the table is structured currently, it is easy to query, is normalized. If you do what you are proposing, it can lead to problems. For example, you will have to know in advance how many rows with a given name would be in the data to determine how many columns your new table will need.

musclebreast
Yak Posting Veteran

77 Posts

Posted - 2014-12-17 : 09:39:12

Hi James,
thanks for your feedback. I think my way should be a FULL Join. For some reason it works not perfect. I ave two table:



(Select 

a.DataID, 
a.ParentID, 
a.SubType, 
a.Name


From

(select 

A1.ID as DataID,   
A1.ParentKeyID,   
A2.ParentID, 
A2.SubType, 
A2.Name, 
A1.DefID, 
A1.ValStr as "Gruppenuebergreifende_Geltung" 
	
from LLAttrData A1, DTree A2

where A1.DefID = %2
and A1.AttrID = %3 
and A1.ID = A2.DataID   
and A1.VerNum = A2.VersionNum
and 

(A1.ValStr = %9 or A1.ValStr = %10 or A1.ValStr = %11 or A1.ValStr = %12 or A1.ValStr = %13 or A1.ValStr = %14 or A1.ValStr = %15 or A1.ValStr = %15 or A1.ValStr = %16 or A1.ValStr = %17 or A1.ValStr = %18)) a



inner join  

(select 

A3.ID as DataID,   
A3.ParentKeyID,   
A4.ParentID, 
A4.SubType, 
A4.Name, 
A3.DefID, 
A3.ValStr as "Gruppenunternehmen" 
	
from LLAttrData A3, DTree A4

where A3.DefID = %2
and A3.AttrID = %4 
and A3.ID = A4.DataID   
and A3.VerNum = A4.VersionNum
and (A3.ValStr = %19 or A3.ValStr = %20 or A3.ValStr = %21 or A3.ValStr = %22 or A3.ValStr = %23 or A3.ValStr = %24 or A3.ValStr = %25 or A3.ValStr = %26 or A3.ValStr = %27 or A3.ValStr = %28)) b

on a.DataID = b.DataID 

inner join 

(select 

A5.ID as DataID,   
A5.ParentKeyID,   
A6.ParentID, 
A6.SubType, 
A6.Name, 
A5.DefID, 
A5.ValStr as "Funktionsbereich" 
	
from LLAttrData A5, DTree A6

where A5.DefID = %2
and A5.AttrID = %5 
and A5.ID = A6.DataID   
and A5.VerNum = A6.VersionNum
and (A5.ValStr = %29 or A5.ValStr = %30 or A5.ValStr = %31 or A5.ValStr = %32 or A5.ValStr = %33 or A5.ValStr = %34 or A5.ValStr = %35 or A5.ValStr = %36 or A5.ValStr = %37 or A5.ValStr = %38)) c


on a.DataID = c.DataID 



inner join 

(select 

A7.ID as DataID,   
A7.ParentKeyID,   
A8.ParentID, 
A8.SubType, 
A8.Name, 
A7.DefID, 
A7.ValStr as "Abteilung" 
	
from LLAttrData A7, DTree A8

where A7.DefID = %2
and A7.AttrID = %6 
and A7.ID = A8.DataID   
and A7.VerNum = A8.VersionNum
and (A7.ValStr = %39 or A7.ValStr = %40 or A7.ValStr = %41 or A7.ValStr = %42 or A7.ValStr = %43 or A7.ValStr = %44 or A7.ValStr = %45 or A7.ValStr = %46 or A7.ValStr = %47 or A7.ValStr = %48)) d


on a.DataID = d.DataID 



inner join 

(select 

A9.ID as DataID,   
A9.ParentKeyID,   
A10.ParentID, 
A10.SubType, 
A10.Name, 
A9.DefID, 
A9.ValStr as "Arbeitsplatz" 
	
from LLAttrData A9, DTree A10

where A9.DefID = %2
and A9.AttrID = %7 
and A9.ID = A10.DataID   
and A9.VerNum = A10.VersionNum
and (A9.ValStr = %49 or A9.ValStr = %50 or A9.ValStr = %51 or A9.ValStr = %52 or A9.ValStr = %53 or A9.ValStr = %54 or A9.ValStr = %55 or A9.ValStr = %56 or A9.ValStr = %57 or A9.ValStr = %58)) e


on a.DataID = e.DataID 




inner join 

(select 

A11.ID as DataID,   
A11.ParentKeyID,   
A12.ParentID, 
A12.SubType, 
A12.Name, 
A11.DefID, 
A11.ValStr as "Hierarchiestufe" 
	
from LLAttrData A11, DTree A12

where A11.DefID = %2
and A11.AttrID = %8 
and A11.ID = A12.DataID   
and A11.VerNum = A12.VersionNum
and (A11.ValStr = %59 or A11.ValStr = %60 or A11.ValStr = %61 or A11.ValStr = %62 or A11.ValStr = %63 or A11.ValStr = %64 or A11.ValStr = %65 or A11.ValStr = %66 or A11.ValStr = %67 or A11.ValStr = %68)) f


on a.DataID = f.DataID 







inner join 

(select 

A13.ID as DataID,   
A13.ParentKeyID,   
A14.ParentID, 
A14.SubType, 
A14.Name, 
A13.DefID, 
A13.ValStr as "Dokumentstatus" 
	
from LLAttrData A13, DTree A14

where A13.DefID = %69
and A13.AttrID = %70 
and A13.ID = A14.DataID   
and A13.VerNum = A14.VersionNum
and A13.ValStr = %71) g


on a.DataID = g.DataID 



inner join dtreeancestors an ON an.DataID = a.DataID and an.AncestorID = %1) v

result:



 dataid  	 parentid  	 subtype  	 name 
 134438  	 129574  	 144  	 Configuration Settings2.docx

Second table:


(Select 

ts.DataID, 
ts.ParentID, 
ts.SubType, 
ts.Name


From 

dtree ts 

Where 

ts.parentid=129244


) r

result:


dataid  	 parentid  	 subtype  	 name 

 135480  	 129244  	 2  	 Configuration Settings.docx 
 137652  	 129244  	 2  	 Configuration Settings2.docx

and know my join



Select 

v.name,
r.name,
v.subtype,
r.subtype,
v.dataid,
r.dataid

From 





(Select 

a.DataID, 
a.ParentID, 
a.SubType, 
a.Name


From

(select 

A1.ID as DataID,   
A1.ParentKeyID,   
A2.ParentID, 
A2.SubType, 
A2.Name, 
A1.DefID, 
A1.ValStr as "Gruppenuebergreifende_Geltung" 
	
from LLAttrData A1, DTree A2

where A1.DefID = %2
and A1.AttrID = %3 
and A1.ID = A2.DataID   
and A1.VerNum = A2.VersionNum
and 

(A1.ValStr = %9 or A1.ValStr = %10 or A1.ValStr = %11 or A1.ValStr = %12 or A1.ValStr = %13 or A1.ValStr = %14 or A1.ValStr = %15 or A1.ValStr = %15 or A1.ValStr = %16 or A1.ValStr = %17 or A1.ValStr = %18)) a



inner join  

(select 

A3.ID as DataID,   
A3.ParentKeyID,   
A4.ParentID, 
A4.SubType, 
A4.Name, 
A3.DefID, 
A3.ValStr as "Gruppenunternehmen" 
	
from LLAttrData A3, DTree A4

where A3.DefID = %2
and A3.AttrID = %4 
and A3.ID = A4.DataID   
and A3.VerNum = A4.VersionNum
and (A3.ValStr = %19 or A3.ValStr = %20 or A3.ValStr = %21 or A3.ValStr = %22 or A3.ValStr = %23 or A3.ValStr = %24 or A3.ValStr = %25 or A3.ValStr = %26 or A3.ValStr = %27 or A3.ValStr = %28)) b

on a.DataID = b.DataID 

inner join 

(select 

A5.ID as DataID,   
A5.ParentKeyID,   
A6.ParentID, 
A6.SubType, 
A6.Name, 
A5.DefID, 
A5.ValStr as "Funktionsbereich" 
	
from LLAttrData A5, DTree A6

where A5.DefID = %2
and A5.AttrID = %5 
and A5.ID = A6.DataID   
and A5.VerNum = A6.VersionNum
and (A5.ValStr = %29 or A5.ValStr = %30 or A5.ValStr = %31 or A5.ValStr = %32 or A5.ValStr = %33 or A5.ValStr = %34 or A5.ValStr = %35 or A5.ValStr = %36 or A5.ValStr = %37 or A5.ValStr = %38)) c


on a.DataID = c.DataID 



inner join 

(select 

A7.ID as DataID,   
A7.ParentKeyID,   
A8.ParentID, 
A8.SubType, 
A8.Name, 
A7.DefID, 
A7.ValStr as "Abteilung" 
	
from LLAttrData A7, DTree A8

where A7.DefID = %2
and A7.AttrID = %6 
and A7.ID = A8.DataID   
and A7.VerNum = A8.VersionNum
and (A7.ValStr = %39 or A7.ValStr = %40 or A7.ValStr = %41 or A7.ValStr = %42 or A7.ValStr = %43 or A7.ValStr = %44 or A7.ValStr = %45 or A7.ValStr = %46 or A7.ValStr = %47 or A7.ValStr = %48)) d


on a.DataID = d.DataID 



inner join 

(select 

A9.ID as DataID,   
A9.ParentKeyID,   
A10.ParentID, 
A10.SubType, 
A10.Name, 
A9.DefID, 
A9.ValStr as "Arbeitsplatz" 
	
from LLAttrData A9, DTree A10

where A9.DefID = %2
and A9.AttrID = %7 
and A9.ID = A10.DataID   
and A9.VerNum = A10.VersionNum
and (A9.ValStr = %49 or A9.ValStr = %50 or A9.ValStr = %51 or A9.ValStr = %52 or A9.ValStr = %53 or A9.ValStr = %54 or A9.ValStr = %55 or A9.ValStr = %56 or A9.ValStr = %57 or A9.ValStr = %58)) e


on a.DataID = e.DataID 




inner join 

(select 

A11.ID as DataID,   
A11.ParentKeyID,   
A12.ParentID, 
A12.SubType, 
A12.Name, 
A11.DefID, 
A11.ValStr as "Hierarchiestufe" 
	
from LLAttrData A11, DTree A12

where A11.DefID = %2
and A11.AttrID = %8 
and A11.ID = A12.DataID   
and A11.VerNum = A12.VersionNum
and (A11.ValStr = %59 or A11.ValStr = %60 or A11.ValStr = %61 or A11.ValStr = %62 or A11.ValStr = %63 or A11.ValStr = %64 or A11.ValStr = %65 or A11.ValStr = %66 or A11.ValStr = %67 or A11.ValStr = %68)) f


on a.DataID = f.DataID 







inner join 

(select 

A13.ID as DataID,   
A13.ParentKeyID,   
A14.ParentID, 
A14.SubType, 
A14.Name, 
A13.DefID, 
A13.ValStr as "Dokumentstatus" 
	
from LLAttrData A13, DTree A14

where A13.DefID = %69
and A13.AttrID = %70 
and A13.ID = A14.DataID   
and A13.VerNum = A14.VersionNum
and A13.ValStr = %71) g


on a.DataID = g.DataID 



inner join dtreeancestors an ON an.DataID = a.DataID and an.AncestorID = %1) v



FULL OUTER JOIN

(Select 

ts.DataID, 
ts.ParentID, 
ts.SubType, 
ts.Name


From 

dtree ts 

Where 

ts.parentid=129244


) r


ON r.name=v.name

the result:



 name  	                          name  	               subtype  	                  subtype  	               dataid  	                        dataid 
 Configuration Settings.docx  	 Configuration Settings.docx  	 2  	 2  	 135480  	 135480 
 Configuration Settings2.docx  	 Configuration Settings2.docx  	 2  	 2  	 137652  	 137652

for some reason the join takes only the values of the second table as subtype, dataid and parentid are not different.

Has anyboy a hin what might be a reason why my full join doesn't work correctly?

Best regards,

Lara

musclebreast
Yak Posting Veteran

77 Posts

Posted - 2014-12-17 : 09:46:05

Ok, I know why. The reason are the same column names in the second table.

CHeers.

Lara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources