SQLTeam.com | Weblogs | Forums

Adding Columns to the Right


#1

I'm very new to the forum, and intend to utilize and take advantage of the community. Hopefully, over time, I can contribute.

I have a database that I managed to create the following list of assemblies that have 'Description1'

SELECT
a.AssemblyName,
CAST(uav.StringValue as nvarchar(255)) AS AttributeValue
FROM dbo.GEN_vAssembly a
LEFT JOIN dbo.GEN_tblUserAttributes uad ON uad.Name = 'Description1'
LEFT JOIN dbo.GEN_tblUserAttributeAssignments asa ON asa.UserAttributeSourceID = -1 AND asa.BuildStrategyGUID = a.BuildStrategyGUID AND uad.UserAttributeGUID = asa.UserAttributeGUID
LEFT JOIN dbo.GEN_tblAssemblyUserAttributeValues uav ON a.AssemblyGUID = uav.AssemblyGUID AND uav.UserAttributeAssignmentGUID = asa.UserAttributeAssignmentGUID
WHERE a.BuildStrategyIsPrimary = 1 AND a.AssemblyName Like '6502-111-%'

and another to give me the same list of assemblies with the 'REV'

SELECT
a.AssemblyName,
CAST(uav.StringValue as nvarchar(255)) AS AttributeValue
FROM dbo.GEN_vAssembly a
LEFT JOIN dbo.GEN_tblUserAttributes ua ON ua.Name = 'REV'
LEFT JOIN dbo.GEN_tblUserAttributeAssignments asa ON asa.UserAttributeSourceID = -1 AND asa.BuildStrategyGUID = a.BuildStrategyGUID AND ua.UserAttributeGUID = asa.UserAttributeGUID
LEFT JOIN dbo.GEN_tblAssemblyUserAttributeValues uav ON a.AssemblyGUID = uav.AssemblyGUID AND uav.UserAttributeAssignmentGUID = asa.UserAttributeAssignmentGUID
WHERE a.BuildStrategyIsPrimary = 1 AND a.AssemblyName Like '6502-111-%'
ORDER BY a.AssemblyName

Both scripts run well, but I'm trying to get an output of 'AssemblyName' 'Description1' and 'REV' as each columns.

When I use 'UNION ALL', it outputs two columns with the 'AssemblyName' instances populated twice in the one column.

I have done some dearching, but I honestly don't know what I'm looking for yet? What functions to search for, and what the correct syntax to use and so on. Out of necessity, I'm hoping I will gain the knowledge I need.

Appreciate any advice/help whatsoever.

KP


#2

can you please post an example of what you're getting and what you want to get?


#3

Here is a snapshot of what I'm getting returned:

AssemblyName Column1
6502-111-DRAWING INDEX
6502-111-DRAWING INDEX 1
6502-111-ST201A FR. 2 STBD BS INBD
6502-111-ST201A 1
6502-111-ST202A FR. 2 PORT BS INBD
6502-111-ST202A 1
6502-111-ST203A 1
6502-111-ST203A FR3 BS CL
6502-111-ST204A FR3+400 ASSEMBLY
6502-111-ST204A 1
6502-111-ST205A 1
6502-111-ST205A FR2+400 BS CL
6502-111-ST206A FR. 3+400 STBD
6502-111-ST206A 1


#4

And here is a snapshot of what I'd like to see, if at all possible:

AssemblyName Column1 Column2
6502-111-DRAWING INDEX 1
6502-111-ST201A FR. 2 STBD BS INBD 1
6502-111-ST202A FR. 2 PORT BS INBD 1
6502-111-ST203A FR3 BS CL 1
6502-111-ST204A FR3+400 ASSEMBLY 1
6502-111-ST205A FR2+400 BS CL 1
6502-111-ST206A FR. 3+400 STBD 1


#5

unfortunately not formatting really well for me.


#6

wanting something like this:


#7

instead of UNION why not:

LEFT JOIN dbo.GEN_tblUserAttributes uad ON uad.Name in ('Description1', 'REV')

???


#8

You mean like this?

SELECT

a.AssemblyName,

CAST(uav.StringValue as nvarchar(255)) AS AttributeValue

FROM dbo.GEN_vAssembly a

LEFT JOIN dbo.GEN_tblUserAttributes uad ON uad.Name in ('Description1', 'REV')

LEFT JOIN dbo.GEN_tblUserAttributes ua ON ua.Name = 'REV'

LEFT JOIN dbo.GEN_tblUserAttributeAssignments asa ON asa.UserAttributeSourceID = -1 AND asa.BuildStrategyGUID = a.BuildStrategyGUID AND uad.UserAttributeGUID = asa.UserAttributeGUID

LEFT JOIN dbo.GEN_tblAssemblyUserAttributeValues uav ON a.AssemblyGUID = uav.AssemblyGUID AND uav.UserAttributeAssignmentGUID = asa.UserAttributeAssignmentGUID

WHERE a.BuildStrategyIsPrimary = 1 AND a.AssemblyName Like '6502-111-%'

ORDER BY a.AssemblyName

This still only gives two columns. I'm guessing because the tables were created where the 'REV' and the 'Description1' both have 'AttributeValue' as a column name? This is what gets returned with your suggestion.


#9

not quite. You don't need:

LEFT JOIN dbo.GEN_tblUserAttributes uad ON uad.Name in ('Description1', 'REV')

LEFT JOIN dbo.GEN_tblUserAttributes ua ON ua.Name = 'REV'

just

LEFT JOIN dbo.GEN_tblUserAttributes uad ON uad.Name in ('Description1', 'REV')

Also, there's nothing in the join to hook it up to the main table GEN_vAssembly. What column do then have in common?


#10

yeah I caught your first catch.

I think the catch here, is that both attributes are coming from the same table, if I said that correctly.

Here is one of the tables the system creates:


#11

and here is another table the system creates:


#12

What if you comment that join and add a where clause:

and uad.Name in ('Description1', 'REV')


#13

I'm getting a 'Incorrect syntax near the keyword 'AND'.' message. So I'm not picking up everything that you're laying down.


#14

post your modified query


#15

SELECT

a.AssemblyName,

CAST(uav.StringValue as nvarchar(255)) AS AttributeValue

FROM dbo.GEN_vAssembly a

LEFT JOIN dbo.GEN_tblUserAttributes uad AND uad.Name in ('Description1', 'REV')

LEFT JOIN dbo.GEN_tblUserAttributeAssignments asa ON asa.UserAttributeSourceID = -1 AND asa.BuildStrategyGUID = a.BuildStrategyGUID AND uad.UserAttributeGUID = asa.UserAttributeGUID

LEFT JOIN dbo.GEN_tblAssemblyUserAttributeValues uav ON a.AssemblyGUID = uav.AssemblyGUID AND uav.UserAttributeAssignmentGUID = asa.UserAttributeAssignmentGUID

WHERE a.BuildStrategyIsPrimary = 1 AND a.AssemblyName Like '6502-111-%'

ORDER BY a.AssemblyName

thanks for your patience


#16

more like this:

SELECT

a.AssemblyName,

CAST(uav.StringValue as nvarchar(255)) AS AttributeValue
FROM dbo.GEN_vAssembly a

LEFT JOIN dbo.GEN_tblUserAttributes uad ON a.? = uad.?

LEFT JOIN dbo.GEN_tblUserAttributeAssignments asa ON asa.UserAttributeSourceID = -1 AND asa.BuildStrategyGUID = a.BuildStrategyGUID AND uad.UserAttributeGUID = asa.UserAttributeGUID

LEFT JOIN dbo.GEN_tblAssemblyUserAttributeValues uav ON a.AssemblyGUID = uav.AssemblyGUID AND uav.UserAttributeAssignmentGUID = asa.UserAttributeAssignmentGUID

WHERE a.BuildStrategyIsPrimary = 1 AND a.AssemblyName Like '6502-111-%'
AND uad.Name in ('Description1', 'REV')

ORDER BY a.AssemblyName

but you need to put something where the ? mark is.


#17

So I thought that by replacing your '?' with 'Name' would do the trick based on the table snippet I passed along. I received an 'Invalid column name 'Name''

Maybe I'll just have to pull from the database with two different scripts, one for 'REV' and the other for 'Description1'


#18

YOu could do that. but please more the check to the where clause and remove the extra join.