structure SQL output - 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

SQL Server 2005 Forums

Transact-SQL (2005)

structure SQL output

Author

Topic

wibni
Starting Member

31 Posts

Posted - 2010-05-03 : 03:18:23

Hello all,

I'm using SQL 2005.

Hello,

I have 1 table which contains asset information.
An asset can be linked to 1 other asset making one the assets a master asset and the other the component asset.
A master asset can be linked to another master asset.

I now want to create a SQL statement which shows me the hierarchy of assets.

I have the following statement:


SELECT "AMASST"."MSTNO" as MasterNumber, "AMASST_1"."DESC" as MasterDesc, "AMASST"."ASTNO" as ComponentNumber, "AMASST"."DESC" AS ComponentDesc
 FROM   "cap100"."dbo"."AMASST" "AMASST" INNER JOIN "cap100"."dbo"."AMASST" "AMASST_1" ON "AMASST"."MSTNO"="AMASST_1"."ASTNO"

This gives me this result:
MasterNumber | MasterDesc | ComonentNumber | ComponentDesc
SIT-0003 | site | CAM-0001 | cam1
SIT-0003 | site | RIG-0002 | rig
RIG-0002 | rig | CAM-0002 | cam2

The result I'd like to see shows the hierarchy of assets.
MasterNumber | MasterDesc | ComonentNumber | ComponentDesc
SIT-0003 | site | CAM-0001 | cam1
SIT-0003 | site | RIG-0002 | rig
----- | ------- | CAM-0002 | cam2

All assets have SIT-0003 as the master asset. They are linked to it either directly (CAM-0001 and RIG-0002) or they are linked to it through another asset (CAM-0002 is linked to SIT-0003 through RIG-0002)

Would anyone be able to tell me whether I can achieve this with SQL and how I have to alter my querry?
Any help much appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-03 : 10:01:47

this seems like a presentation issue. is this for a sql report?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

wibni
Starting Member

31 Posts

Posted - 2010-05-03 : 10:20:33

Thanks for your reply.

Yes, an SQL report.
I can't get my head around this SQL statement which I think shouldn't be so hard.

This is the table as I have it:

Asset | Masterasset
--------|-------------
Asset1 | Masterasset1
Asset2 | Masterasset1
Asset3 | Asset2
Asset4 | Asset2

In the result I want to show the hierarchie of the assets.

Asset | Masterasset
--------|-------------
Asset1 | Masterasset1
Asset2 |
Asset3 |
Asset4 |

Asset2 and Asset3 aren't linked to Masterasset1 directly. They are linked to Asset2 and Asset2 is linked to Masterasset1.
But I only want to know which assets and 'sub-assets' are linked to a masterasset.

This seems to be a common problem, but I can't work it out. ;(

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-03 : 10:23:46

you can get this format very easily in SQL reports. you need to set parent hierarchy field as Masterasset to get this.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

wibni
Starting Member

31 Posts

Posted - 2010-05-03 : 10:31:48

Sorry, what I meant with report is creating an SQL report in Crystal Reports.
But I need to understand the SQL, cause I'm completly new to Crystal.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-03 : 10:54:51

then use recursive cte and take the counts of descendants

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

wibni
Starting Member

31 Posts

Posted - 2010-05-03 : 11:05:13

Hello,

tried that as well but cannot find a way to go past the 1st level.
It will list me Asset1 and Asset2 but Asset3 and Asset4 are missing because they are not linked to Masterasset1 directly.
They are only linked to it via Asset2.


USE [cap100]
go
WITH Sites (mstno, astno)
AS
(
    -- Anchor member definition
    SELECT mstno, astno
    FROM AMASST where mstno = 'Masterasset1'
    UNION ALL

    -- Recursive member definition
    SELECT c.mstno, c.astno From amasst as c
    inner JOIN Sites as p
    ON c.astno = p.mstno
)

-- Statement that executes the CTE
SELECT * from Sites

wibni
Starting Member

31 Posts

Posted - 2010-05-03 : 21:17:50

This one works.
Thanks



WITH Sites (mstno, astno, rootasst)
AS
(
    -- Anchor member definition
    SELECT mstno, astno ,mstno as RootAsst
    FROM AMASST where mstno <>''
    UNION ALL

    -- Recursive member definition
    SELECT c.mstno, c.astno,p.rootasst From amasst as c
    inner JOIN Sites as p
    ON p.astno = c.mstno
)

-- Statement that executes the CTE
SELECT * from Sites order by rootasst desc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-04 : 04:03:16

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

wibni
Starting Member

31 Posts

Posted - 2010-05-04 : 09:30:28

Hmm, something still not quite working.

I'm trying to get the description for ROOTASST. Thought that's straight forward but its given me the wrong description under 'p.MasterDesc'.
Do I need to add another Join just for the description? That doesn't seem right...


WITH Sites(MSTNO, ASTNO, [DESC], ACQDATE, ROOTASST, MasterDesc) AS 
        (SELECT MSTNO, ASTNO, [DESC], ACQDATE, MSTNO AS RootAsst, [DESC] AS MasterDesc
     FROM   dbo.AMASST
     WHERE  (MSTNO <> '')
 UNION ALL
     SELECT     c.MSTNO, c.ASTNO, c.[DESC], c.ACQDATE, p.RootAsst, p.MasterDesc
     FROM         dbo.AMASST AS c INNER JOIN Sites AS p ON p.astno = c.MSTNO)
    
SELECT * FROM Sites AS Sites_1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-04 : 12:30:57

can you show some sample data and then explain?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

wibni
Starting Member

31 Posts

Posted - 2010-05-05 : 04:05:28

Hello,

thanks for your reply.

Hi Dave,

thank you for your quick reply.

This is my table:

Asset | Masterasset | Desc
-------|--------------|------
Masterasset1| ------- | DescMasterasset1
Asset1 | Masterasset1 | DescAsset1
Asset2 | Masterasset1 | DescAsset2
Asset3 | Asset2 | DescAsset3
Asset4 | Asset2 | DescAsset4

Asset3 and Asset4 aren't linked to Masterasset1 directly. They are linked to Asset2 and Asset2 is linked to Masterasset1.

The result I get with the recursive CTE is this:

RootAsst | Masterrasset | Asset | Desc | MasterDesc
-------------|----------|------|------|-----------
Masterasset1 | Masterasset1 | Asset1 | DescAsset1 | DescAsset1
Masterasset1 | Masterasset1 | Asset2 | DescAsset1 | DescAsset2
Masterasset1 | Asset2 | Asset3 | DescAsset1 | DescAsset2
Masterasset1 | Asset2 | Asset4 | DescAsset1 | DescAsset2

My probelem now are the descriptions of the RootAsst under column MasterDesc.
They should be the decription of the RootAsst, not the Asset (red) or Masterasset (blue).
The MasterDesc should be 'DescMasterAsset1' in all 4 cases.
I thought that's what I've done in my query with the JOIN, but it won't work.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources