December 18, 2011 at 10:34 pm
Hi,
I need to create a view for the below table. This table is from our document management system.
LEVEL1LEVEL2LEVEL3LEVELIDCODEDESC CUSTNODEPTDEPT3
4313001431350075Cust ABC 50075
431343140243141000Dept 1 500751000
431343144505345051001Dept 1 - Sub 15007510001001
431343144518345181002Dept 1 - Sub 25007510001002
431343144519345191003Dept 1 - Sub 35007510001003
431343150243152000Dept 2 500752000
431343154478344782001Dept 2 - Sub 15007520002001
431343154503345032002Dept 2 - Sub 25007520002002
431343154504345042003Dept 2 - Sub 35007520002003
431343154564345642004Dept 2 - Sub 45007520002004
431343155034350342005Dept 2 - Sub 55007520002005
431347450247453000Dept 3 500753000
431347454746347463001Dept 3 - Sub15007530003001
431347454747347473002Dept 3 - Sub25007530003002
431349080249089999Dept 4 500759999
431349084922349221Dept 4 - Sub 15007599991
431349084923349232Dept 4 - Sub 25007599992
431349084935349353Dept 4 - Sub 35007599993
431351650251651009Dept 5 500751009
7600001760050095Cust XYZ 500951000
760076010276011000Dept 1 500951000
760076017610376101001Dept 1 - Sub 15009510001001
760076200276202000Dept 2 500952000
8100001810062000Cust QRS 61025
The ouput should be like this.
Customer DepartmentSubDepartment
Cust ABC Dept 1 Dept 1 - Sub 1
Cust ABC Dept 1 Dept 1 - Sub 2
Cust ABC Dept 1 Dept 1 - Sub 3
Cust ABC Dept 2 Dept 2 - Sub 1
Cust ABC Dept 2 Dept 2 - Sub 2
Cust ABC Dept 2 Dept 2 - Sub 3
Cust ABC Dept 2 Dept 2 - Sub 4
Cust ABC Dept 2 Dept 2 - Sub 5
Cust ABC Dept 3 Dept 3 - Sub1
Cust ABC Dept 3 Dept 3 - Sub2
Cust ABC Dept 4 Dept 4 - Sub 1
Cust ABC Dept 4 Dept 4 - Sub 2
Cust ABC Dept 4 Dept 4 - Sub 3
Cust ABC Dept 5
Cust XYZ Dept 1 Dept 1 - Sub 1
Cust XYZ Dept 2
Cust QRS
December 18, 2011 at 10:56 pm
The indention of each column is not taken when i post it, I have added a comma seperator so you can copy and paste, and save it as CSV to see it clearly.
LEVEL1,LEVEL2,LEVEL3,LEVEL,ID,CODE,DESC, CUSTNO,DEPT,DEPT3,
4313,0,0,1,4313,50075,Cust ABC,50075,,,
4313,4314,0,2,4314,1000,Dept 1, 50075,1000,,
4313,4314,4505,3,4505,1001,Dept 1 - Sub 1,50075,1000,1001,
4313,4314,4518,3,4518,1002,Dept 1 - Sub 2,50075,1000,1002,
4313,4314,4519,3,4519,1003,Dept 1 - Sub 3,50075,1000,1003,
4313,4315,0,2,4315,2000,Dept 2, 50075,2000,,
4313,4315,4478,3,4478,2001,Dept 2 - Sub 1,50075,2000,2001,
4313,4315,4503,3,4503,2002,Dept 2 - Sub 2,50075,2000,2002,
4313,4315,4504,3,4504,2003,Dept 2 - Sub 3,50075,2000,2003,
4313,4315,4564,3,4564,2004,Dept 2 - Sub 4,50075,2000,2004,
4313,4315,5034,3,5034,2005,Dept 2 - Sub 5,50075,2000,2005,
4313,4745,0,2,4745,3000,Dept 3, 50075,3000,,
4313,4745,4746,3,4746,3001,Dept 3 - Sub1,50075,3000,3001,
4313,4745,4747,3,4747,3002,Dept 3 - Sub2,50075,3000,3002,
4313,4908,0,2,4908,9999,Dept 4, 50075,9999,,
4313,4908,4922,3,4922,1,Dept 4 - Sub 1,50075,9999,1,
4313,4908,4923,3,4923,2,Dept 4 - Sub 2,50075,9999,2,
4313,4908,4935,3,4935,3,Dept 4 - Sub 3,50075,9999,3,
4313,5165,0,2,5165,1009,Dept 5, 50075,1009,,
7600,0,0,1,7600,50095,Cust XYZ,50095,1000,,
7600,7601,0,2,7601,1000,Dept 1, 50095,1000,,
7600,7601,7610,3,7610,1001,Dept 1 - Sub 1,50095,1000,1001,
7600,7620,0,2,7620,2000,Dept 2, 50095,2000,,
8100,0,0,1,8100,62000,Cust QRS,61025,,,
December 19, 2011 at 1:40 am
Hi
Can you set up your sample data for easy consumption, like this:
CREATE TABLE #Sampledata (LEVEL1 datatype, LEVEL2 datatype, LEVEL3 datatype, LEVEL datatype, ID datatype, datatype, DESC datatype, CUSTNO datatype, DEPT datatype, DEPT3 datatype)
INSERT INTO #Sampledata (LEVEL1, LEVEL2, LEVEL3, LEVEL, ID, , DESC, CUSTNO, DEPT, DEPT3)
SELECT 4313, 0, 0, 1, 4313, 50075, 'Cust ABC', 50075, , , UNION ALL
......
Put in the correct datatype for each column and don't forget to put single quotes around character data values in the INSERTs.
Which columns did you use to order this data set?
Cheers
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
December 19, 2011 at 3:23 am
Got a help from a colleague, it is something like this.
Thanks anyway.
Select CODE ,[DESC] as subDepart,
(Select [DESC] from dbo.CUSTOMER as c1 where c1.LEVEL1 = c.LEVEL1 and c1.LEVEL = 1 ) as Client,
(Select [DESC] from dbo.CUSTOMER as c1 where c1.LEVEL2 = c.LEVEL2 and c1.LEVEL = 2 ) as Department
from dbo.CUSTOMER as c
where LEVEL = 3
union
Select CODE ,'' as SubDepart,
(Select [DESC] from dbo.CUSTOMER as c1 where c1.LEVEL1 = c.LEVEL1 and c1.LEVEL = 1 ) as Client,
[desc]
from dbo.CUSTOMER as c
where LEVEL = 2
and LEVEL2 not in (select LEVEL2 from dbo.CUSTOMER as C2 where c2.LEVEL2 = c.LEVEL2 and LEVEL = 3)
union
Select CODE ,'' as SubDepart,
[Desc] as client,
'' as Department
from dbo.CUSTOMER as c
where LEVEL = 1
and LEVEL1 not in (select LEVEL1 from dbo.CUSTOMER as C2 where c2.LEVEL1 = c.LEVEL1 and LEVEL in (2,3))
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply