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.
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -