Hierachy query

Question

Hierachy query

Hadrian

Hall of Fame

Points: 3742
More actions
December 20, 2011 at 2:53 am

#248404

I have two tables let's say questions with questionID ad structureID and other Strcuture with StructureID and level columns.
I have to solve this problem: When the question is assigned to LPLUS to the Level 1 (@1) then level 1 should be described only.
When the question is assigned to the Level 2 (@2) then level 1 and 2 should be described.
When the question is assigned to the Level 3 (@3) then level 1, 2, and 3 should be described.
When the question is assigned to the Level 4 (@4) then level 1, 2, 3 and 4 should be described.
When the question is assigned to the Level 5 (@5) then level 1, 2, 3, 4, and 5 should be described.
May you help me with with query which give me the expected result set?
Thanks,
Hadrian

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

DDL? Sample data?[/url]

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Hadrian Hall of Fame Points: 3742 More actions · Answer 2

Table questiions sample data

questionIDstructureID

81435

81537

838136

8401191

8411191

8421191

843155

845155

846155

8471237

81838

81938

82032

82238

82340

82441

82641

82742

Table Structure

structureIDTEXtaltText

1090AMC M.A.202 - Occurence reporting@5-

1091Subpart C - Continuing Airworthiness@4-

1092AMC M.A.301 - Continuing airworthiness tasks@5-

1093AMC M.A.302 - Maintenance programme@5-

1095AMC M.A.304 - Data for modifications and repairs@5-

1096AMC M.A.305 - Aircraft continuing airworthiness record system@5-

1097AMC M.A.306 - Operator's technical log system@5-

1098AMC M.A.307 - Transfer of aircraft continuing airworthiness records@5-

1099Subpart D - Maintenance Standards@4-

1100AMC M.A.401 - Maintenance data@5-

1101AMC M.A.402 - Performance of maintenance@5-

1102AMC M.A.403 - Aircraft defects@5-

1103Subpart E - Components@4-

1104AMC M.A.501 - Installation@5-

1107AMC M.A.504 - Control of unserviceable components@5-

1108Subpart F - Maintenance organisation@4-

1109AMC M.A.601 - Scope@5-

1110AMC M.A.602 - Application@5-

1111AMC M.A.603 - Extent of approval@5-

1112AMC M.A.604 - Maintenance organisation manual@5-

1113AMC M.A.605 - Facilities@5-

1114AMC M.A.606 - Personnel requirements@5-

1115AMC M.A.607 - Certifying staff@5-

1802Appendix IX to EASA Form 2@3-

1804Appendix XI to AMC to M.A.708(c)@3-

1808Section B Procedure for Competent Authorities@3-

1116AMC M.A.608 - Components, equipment and tools@5-

1117AMC M.A.609 - Maintenance data@5-

1121AMC M.A.613 - Component certificate of release to service@5-

1122AMC M.A.614 - Maintenance records@5-

1124AMC M.A.616 - Organisational review@5-

1125AMC M.A.617 - Changes to the approved maintenance organisation@5-

1128Subpart G - Continuing Airworthiness Management Organisation@4-

1132AMC M.A.704 - Continuing airworthiness management exposition@5-

1133AMC M.A.705 - Facilities@5-

1134AMC M.A.706 - Personnel requirements@5-

1135AMC M.A.707 - Airworthiness review staff@5-

1136AMC M.A.708 - Continuing airworthiness management@5-

1138AMC M.A.710 - Airworthiness review@5-

1139AMC M.A.711 - Privileges of the organisation@5-

1140AMC M.A.712 - Quality system@5-

1141AMC M.A.713 - Changes to the approved continuing airworthiness organisation@5-

1142AMC M.A.714 - Record-keeping@5-

1145Subpart H - Certificate of release to service - CRS@4-

1146AMC M.A.801 - Aircraft certificate of release to service@5-

1147AMC M.A.802 - Component certificate of release to service@5-

1148AMC M.A.803 - Pilot-owner authorisation@5-

1149Subpart I - Airworthiness review certificate@4-

1150AMC M.A.901 - Aircraft airworthiness review@5-

1152AMC M.A.903 - Transfer of aircraft registration within the EU@5-

1153AMC M.A.904 - Airworthiness review of aircraft imported into the EU@5-

1155Subpart A - General@4-

1157AMC M.B.102 - Competent authority@5-

1159AMC M.B.104 - Record-keeping@5-

1160AMC M.B.105 - Mutual exchange of information@5-

1161Subpart B - Accountability@4-

1163Subpart C - Continuing Airworthiness@4-

1165AMC M.B.301 - Maintenance programme@5-

1805Appendix X to AMC EASA Form 4@3-

1807Section A Technical Requirements@3-

1809Section A@3-

1814Appendix IV - EASA Form 12@3-

1819Appendix I@3-

1820Appendix II@3-

1821Appendix III@3-

1166AMC M.B.303 - Aircraft continuing airworthiness monitoring@5-

1168Subpart D - Maintenance standards@4-

1169Subpart E - Components@4-

1170Subpart F - Maintenance organisation@4-

1172AMC M.B.602 - Initial approval@5-

1173AMC M.B.603 - Issue of approval@5-

1174AMC M.B.604 - Continuing oversight@5-

1175AMC M.B.605 - Findings@5-

1176AMC M.B.606 - Changes@5-

1178Subpart G - Continuing Airworthiness Management Organisation@4-

1179AMC M.B.701 - Application@5-

1180AMC M.B.702 - Initial approval@5-

1181AMC M.B.703 - Issue of approval@5-

1182AMC M.B.704 - Continuing oversight@5-

1183AMC M.B.705 Findings@5-

1184AMC M.B.706 - Changes@5-

1186Subpart H - Certificate of release to servivce - CRS@4-

1187Subpart I - Airworthiness review certificate@4-

1188AMC M.B.901 - Assessment of recommendations@5-

1189AMC M.B.902 - Airworthiness review by the competent authority@5-

1191AMC 145.A.10 - Scope@4-

1815AMC M.A.708 (b)(3) - Continuing Airworthiness Management @5-

1817Appendix XII to AMC to M.A.706 (f) and M.B.102 (c)@3-

1818AMC 145.A.65 (b) (3) - Safety and quality policy, maintenance procedures and quality system@4-

1292AMC 66.B.305 - Conversion report for national qualifications@4-

1293AMC 66.B.310 - Conversion report for approved maintenance organisation authorisation@4-

1299Appendix I - Aircraft type ratings for Part-66 Aircraft Maintenance Licence@3-

1300Appendix II - Aicraft Type Practical Experience - List of tasks@3-

1305Section A@3-

1310GM 66.A.20 - Privileges@4-

1311GM 66.A.25 - Basic knowledge requirements@4-

1312GM 66.A.30 - Experience requirements@4-

1313GM 66.A.40 - Continued validity of the aircraft maintenance licence@4-

1314GM 66.A.45 - Type/task training and ratings@4-

1822Appendix IV to AMC to 145.A.30 (e) and 145.B.10 (3)

1824Commission Regulation (EC) No 1702/2003 of 24 September 2003@1-

1825Article 1 - Scope and definitions@2-

1828Article 2 - Products, parts and appliances certification@2-

1829Article 3 - Design organisations@2-

1830Article 4 - Production organisations@2-

1831Article 5 - Entry into force@2-

1832Annex - PART 21@2-

183321.1 - General@3-

1834SECTION A@3-

1851Requirements for applicants and acquired rights and obligations @4 -

1852Subpart A - General Provisions@4-

1853Subpart B - Type-certificates and restricted type-certificates@4-

1315GM 66.A.70 - Conversion provisions@4-

1320Section B - Procedure for Competent Authorities@3-

1327GM 66.B.30 - Exemptions@4-

1332GM 66.B.115 - Procedure for the amendment of an AML to include an aircraft type or group@4-

1333GM 66.B.120 - Procedure for the renewal of an aircraft maintenance licence validity@4-

1335GM 66.B.200 - Examination by the competent authority@4-

1854Subpart C - Not applicable@4-

1855Subpart D - Changes to type-certificates and restricted type-certificates@4-

1856Subpart E - Supplemental type-certificates@4-

1857Subpart F - Production without Production Organisation Approval@4-

1858Subpart G - Production Organisation Approval@4-

1859Subpart H - Airwothiness Certificates@4-

1860Subpart I - Noise Certificates@4-

1861Subpart J - Design Organisation Approval@4-

1862Subpart K - Parts and Appliances@4-

1863Subpart L - Not applicable@4-

1864Subpart M - Repairs@4-

1865Subpart N - Not applicable@4-

1866Subpart O - European Technical Standard Order Authorisation@4-

1867Subpart P @4-

1868Subpart Q - Identification of products, parts and appliances@4-

1869SECTION B@3-

1870Procedures for competent authorities@4-

1351Section A@3-

1352Section B - Procedure for Competent Authority@3-

1358AMC 147.A.100 - Facility requirements@4-

1359AMC 147.A.105 - Personnel requirements@4-

1361AMC 147.A.115 - Instructional equipment@4-

1362AMC 147.A.120 - Maintenance training material@4-

1364AMC 147. A.130 - Training procedures and quality system@4-

1365AMC 147.A.135 - Examinations@4-

1366AMC 147.A.140 - Maintenance training organisation exposition@4-

1367AMC 147.A.145 - Privileges of the maintenance training organisation@4-

1372AMC 147.A.200 - The approved basic training course@4-

1373AMC 147.A.205 - Basic knowledge examinations@4-

1374AMC 147.A.210 - Basic practical assessment@4-

1376AMC 147.A.300 - Aircraft type/task training@4-

1380AMC 147.B.10 - Competent Authority@4-

1382AMC 147.B.20 - Record-keeping@4-

1871Subpart A - General provisions@4-

1872Subpart B - Type-certificates and restricted type-certificates@4-

1873Subpart C - Not applicable@4-

1874Subpart D - Changes to type-certificates and restricted type-certificates@4-

1875Subpart E - Supplemental type-certificates@4-

1876Subpart F - Production without Production Organisation Approval@4-

1877Subpart G - Production Organisation Approval@4-

1878Subpart H - Airworthiness Certificates@4-

1879Subpart I - Noise Certificates@4-

1880Subpart J - Design Organisation Approval@4-

1881Subpart K - Parts and Appliances@4-

1387AMC 147.B.110 - Approval procedure@4-

1391AMC 147.B.130 - Findings@4-

1394Appendix I - Maintenance training organisation exposition (MTOE)@3-

1395Appendix II - EASA Form 4@3-

1396Appendix III - EASA Form 22@3-

1398Section A@3-

1399Section B - Procedure for Competent Authorities@3-

1402GM 147.A.10 - General@4-

1405GM 147.A.100 - Facility requirements@4-

1406GM 147.A.105 - Personnel requirements@4-

1407GM 147.A.110 - Records of instructors, examiners and assessors@4-

1408GM 147.A.115 - Instructional equipment@4-

1882Subpart L - Not applicable@4-

1883Subpart M - Repairs@4-

1884Subpart N - Not applicable@4-

1885Subpart O - European Technical Standard Order Authorisations@4-

1886Subpart P@4-

1887Subpart Q - Identification of Products, Parts and Appliences@4-

1888APPENDICES - EASA Forms@3-

1889Appendix I - EASA Form 1 Authorised release Certificate@4-

1890Appendix II - EASA Form 15a Airworthiness Review Certificate@4-

1891Appendix III - EASA Form 20 - Permit to Fly@4-

1892Appendix IV - EASA Form 24 - Restricted Certificate of Airworthiness@4-

1893Appendix V - EASA Form 25 Certificate of Airworthiness@4-

1411GM 147. A.130 - Training procedures and quality system@4-

1412GM 147.A.135 - Examinations@4-

1414GM 147.A.145 - Privileges of the maintenance training organisation@4-

1432GM 147.B.100 - General@4-

1434GM 147.B.110 - Approval procedure@4-

1435GM 147.B.115 - Variation procedure@4-

1894Appendix VI - EASA Form 45 - Noise Certificate@4-

1895Appendix VII - EASA Form 52 - Aircraft Statement of Conformity@4-

1896Appendix VIII - EASA Form 53 - Certificate of Release to Service@4-

1897Appendix IX - EASA Form 55 - Production Organisation Approval Certificate@4-

1898Appendix X - EASA Form 65 - Letter of Agreemnet [Production without POA]@4-

1900Commission Regulation (EC) No 375/2007 of 30 March 2007@1-

1901Article 1@2-

1902Article 2@2-

Cadavre SSC-Forever Points: 41690 More actions · Answer 3

Can you read the article I linked to about how best to post sample data and DDL? Personally, I don't really have the time to turn your text data into something usable to help me code up a response to your question.

Cadavre (12/20/2011)
DDL? Sample data?[/url]

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Hadrian Hall of Fame Points: 3742 More actions · Answer 4

Hi again,

I hope now the data are in requested format...

create table #questions1 (questionID int,structureId int)

insert into #questions1

SELECT '814','35' UNION ALL

SELECT '815','37' UNION ALL

SELECT '838','136' UNION ALL

SELECT '840','1191' UNION ALL

SELECT '841','1191' UNION ALL

SELECT '842','1191' UNION ALL

SELECT '843','155' UNION ALL

SELECT '845','155' UNION ALL

SELECT '846','155' UNION ALL

SELECT '847','1237' UNION ALL

SELECT '818','38' UNION ALL

SELECT '819','38' UNION ALL

SELECT '820','32' UNION ALL

SELECT '822','38' UNION ALL

SELECT '823','40' UNION ALL

SELECT '824','41' UNION ALL

SELECT '826','41' UNION ALL

SELECT '827','42' UNION ALL

SELECT '848','1237' UNION ALL

SELECT '1230','3248' UNION ALL

SELECT '829','47' UNION ALL

SELECT '850','140' UNION ALL

SELECT '851','140' UNION ALL

SELECT '852','140' UNION ALL

SELECT '853','1089' UNION ALL

SELECT '854','1136' UNION ALL

SELECT '855','1804' UNION ALL

SELECT '856','1194' UNION ALL

SELECT '857','147' UNION ALL

SELECT '859','1195' UNION ALL

SELECT '860','147' UNION ALL

SELECT '861','1195' UNION ALL

SELECT '830','47' UNION ALL

SELECT '831','47' UNION ALL

SELECT '862','1195' UNION ALL

SELECT '863','1195' UNION ALL

SELECT '865','149' UNION ALL

SELECT '866','1197' UNION ALL

SELECT '867','150' UNION ALL

SELECT '868','1198' UNION ALL

SELECT '869','1198' UNION ALL

SELECT '870','73' UNION ALL

SELECT '871','75' UNION ALL

SELECT '832','76' UNION ALL

SELECT '873','47' UNION ALL

SELECT '874','1199' UNION ALL

SELECT '875','39' UNION ALL

SELECT '876','144' UNION ALL

SELECT '877','146' UNION ALL

SELECT '878','147' UNION ALL

SELECT '879','148' UNION ALL

SELECT '880','153' UNION ALL

SELECT '881','154' UNION ALL

SELECT '883','162' UNION ALL

SELECT '884','165' UNION ALL

SELECT '180','185' UNION ALL

SELECT '885','153' UNION ALL

SELECT '887','153' UNION ALL

SELECT '888','150' UNION ALL

SELECT '889','150' UNION ALL

SELECT '893','1195' UNION ALL

SELECT '894','1229' UNION ALL

SELECT '895','153' UNION ALL

SELECT '896','153' UNION ALL

SELECT '921','230' UNION ALL

SELECT '922','230' UNION ALL

SELECT '230','203' UNION ALL

SELECT '897','153' UNION ALL

SELECT '898','153' UNION ALL

SELECT '901','155' UNION ALL

SELECT '903','155' UNION ALL

SELECT '904','1203' UNION ALL

SELECT '906','1203' UNION ALL

SELECT '924','233' UNION ALL

SELECT '2641','222' UNION ALL

SELECT '2642','222' UNION ALL

SELECT '2643','226' UNION ALL

SELECT '282','136' UNION ALL

SELECT '833','74' UNION ALL

SELECT '910','156' UNION ALL

SELECT '911','156' UNION ALL

SELECT '913','1204' UNION ALL

SELECT '914','1204' UNION ALL

SELECT '915','1204' UNION ALL

SELECT '916','1204' UNION ALL

SELECT '2644','227' UNION ALL

SELECT '2645','1402' UNION ALL

SELECT '835','77' UNION ALL

SELECT '925','236' UNION ALL

SELECT '926','245' UNION ALL

SELECT '1855','3420' UNION ALL

SELECT '1857','3422' UNION ALL

SELECT '1859','3422' UNION ALL

SELECT '2352','3165' UNION ALL

SELECT '2665','240' UNION ALL

SELECT '1256','3208' UNION ALL

SELECT '1257','3211' UNION ALL

SELECT '1258','3211' UNION ALL

SELECT '1259','1877' UNION ALL

SELECT '1260','1858' UNION ALL

SELECT '1261','3215' UNION ALL

SELECT '1262','3216' UNION ALL

SELECT '1263','3217' UNION ALL

SELECT '1264','3220' UNION ALL

SELECT '1265','3223' UNION ALL

SELECT '1266','3224' UNION ALL

SELECT '1221','119' UNION ALL

SELECT '1222','119' UNION ALL

SELECT '1223','119' UNION ALL

SELECT '1224','120' UNION ALL

SELECT '1225','120' UNION ALL

SELECT '1226','121' UNION ALL

SELECT '1227','121' UNION ALL

SELECT '1228','121' UNION ALL

SELECT '1229','122' UNION ALL

SELECT '1231','3250' UNION ALL

SELECT '1232','3250' UNION ALL

SELECT '1575','203' UNION ALL

SELECT '1576','186' UNION ALL

SELECT '1577','17' UNION ALL

SELECT '1578','34' UNION ALL

SELECT '1579','34' UNION ALL

SELECT '1580','81' UNION ALL

SELECT '1581','81' UNION ALL

SELECT '1582','81' UNION ALL

SELECT '1583','81' UNION ALL

SELECT '1584','81' UNION ALL

SELECT '1585','81' UNION ALL

SELECT '1234','3251' UNION ALL

SELECT '1235','3253' UNION ALL

SELECT '1236','3254' UNION ALL

SELECT '1237','3254' UNION ALL

SELECT '1238','3256' UNION ALL

SELECT '1239','3257' UNION ALL

SELECT '1240','3258' UNION ALL

SELECT '1241','3259' UNION ALL

SELECT '1242','3260' UNION ALL

SELECT '1243','3260' UNION ALL

SELECT '1244','3261' UNION ALL

SELECT '1508','1828' UNION ALL

SELECT '1509','1830' UNION ALL

SELECT '1510','1830' UNION ALL

SELECT '1511','1830' UNION ALL

SELECT '1512','1830' UNION ALL

SELECT '1513','3162' UNION ALL

SELECT '1514','3207' UNION ALL

SELECT '1515','3207' UNION ALL

SELECT '1516','3208' UNION ALL

SELECT '1517','3208' UNION ALL

SELECT '1518','3208' UNION ALL

SELECT '1842','219' UNION ALL

SELECT '1843','219' UNION ALL

SELECT '1844','219' UNION ALL

SELECT '1845','231' UNION ALL

SELECT '1846','231' UNION ALL

SELECT '1847','239' UNION ALL

SELECT '1849','266' UNION ALL

SELECT '1850','1372' UNION ALL

SELECT '1851','1372' UNION ALL

SELECT '2648','230' UNION ALL

SELECT '1852','232' UNION ALL

SELECT '1860','3425' UNION ALL

SELECT '1861','3419' UNION ALL

SELECT '1862','3426' UNION ALL

SELECT '1863','3430' UNION ALL

SELECT '2649','228' UNION ALL

SELECT '323','34' UNION ALL

SELECT '327','81' UNION ALL

SELECT '927','246' UNION ALL

SELECT '928','1204' UNION ALL

SELECT '936','1132' UNION ALL

SELECT '998','3147' UNION ALL

SELECT '999','3147' UNION ALL

SELECT '2646','228' UNION ALL

SELECT '328','81' UNION ALL

SELECT '330','81' UNION ALL

SELECT '333','81' UNION ALL

SELECT '334','81' UNION ALL

SELECT '336','81' UNION ALL

SELECT '934','1195' UNION ALL

SELECT '935','136' UNION ALL

SELECT '1864','3431' UNION ALL

SELECT '1903','3498' UNION ALL

SELECT '1949','3558' UNION ALL

SELECT '342','77' UNION ALL

SELECT '347','79' UNION ALL

SELECT '351','79' UNION ALL

SELECT '939','1078' UNION ALL

SELECT '940','1199' UNION ALL

SELECT '949','1268' UNION ALL

SELECT '1865','3432' UNION ALL

SELECT '1904','3498' UNION ALL

SELECT '1958','3566' UNION ALL

SELECT '1986','3500' UNION ALL

SELECT '2647','230' UNION ALL

SELECT '2650','230' UNION ALL

SELECT '1519','3208' UNION ALL

SELECT '1520','3209' UNION ALL

SELECT '1521','3210' UNION ALL

SELECT '1522','3211' UNION ALL

SELECT '1523','3211' UNION ALL

SELECT '1524','3211' UNION ALL

SELECT '1525','3211' UNION ALL

SELECT '1526','3211' UNION ALL

SELECT '1527','3211' UNION ALL

SELECT '1528','3211' UNION ALL

SELECT '1529','3212' UNION ALL

SELECT '355','80' UNION ALL

SELECT '357','97' UNION ALL

SELECT '359','133' UNION ALL

SELECT '942','1822' UNION ALL

SELECT '948','1817' UNION ALL

SELECT '950','1205' UNION ALL

SELECT '951','1818' UNION ALL

SELECT '954','3151' UNION ALL

SELECT '1000','34' UNION ALL

SELECT '1001','34' UNION ALL

SELECT '374','230' UNION ALL

SELECT '955','3154' UNION ALL

SELECT '956','3159' UNION ALL

SELECT '957','3165' UNION ALL

SELECT '958','3224' UNION ALL

SELECT '959','3226' UNION ALL

SELECT '960','3240' UNION ALL

SELECT '961','3249' UNION ALL

SELECT '962','3250' UNION ALL

SELECT '381','230' UNION ALL

SELECT '963','3255' UNION ALL

SELECT '964','3259' UNION ALL

SELECT '965','3264' UNION ALL

SELECT '966','3267' UNION ALL

SELECT '967','3268' UNION ALL

SELECT '968','3269' UNION ALL

SELECT '969','3279' UNION ALL

SELECT '970','3282' UNION ALL

SELECT '2651','230' UNION ALL

SELECT '397','230' UNION ALL

SELECT '971','3290' UNION ALL

SELECT '972','3294' UNION ALL

SELECT '973','3297' UNION ALL

SELECT '409','231' UNION ALL

SELECT '417','231' UNION ALL

SELECT '429','234' UNION ALL

SELECT '1002','34' UNION ALL

SELECT '1003','34' UNION ALL

SELECT '1004','34' UNION ALL

SELECT '1005','34' UNION ALL

SELECT '1006','35' UNION ALL

SELECT '1007','35' UNION ALL

SELECT '1009','37' UNION ALL

SELECT '1102','1089' UNION ALL

SELECT '2652','230' UNION ALL

SELECT '435','235' UNION ALL

SELECT '442','237' UNION ALL

SELECT '444','237' UNION ALL

SELECT '1012','1093' UNION ALL

SELECT '1014','1093' UNION ALL

SELECT '1015','38' UNION ALL

SELECT '1103','1089' UNION ALL

SELECT '454','238' UNION ALL

SELECT '1018','40' UNION ALL

SELECT '1021','41' UNION ALL

SELECT '1022','41' UNION ALL

SELECT '1023','41' UNION ALL

SELECT '1024','41' UNION ALL

SELECT '1025','41' UNION ALL

SELECT '1106','1090' UNION ALL

SELECT '1161','55' UNION ALL

SELECT '1866','3435'

create table #structure1 ( structureID int,

Text varchar(max),altText varchar(max))

insert into #structure1

SELECT '1090','AMC M.A.202 - Occurence reporting','@5- ' UNION ALL

SELECT '1091','Subpart C - Continuing Airworthiness','@4- ' UNION ALL

SELECT '1092','AMC M.A.301 - Continuing airworthiness tasks','@5- ' UNION ALL

SELECT '1093','AMC M.A.302 - Maintenance programme','@5- ' UNION ALL

SELECT '1095','AMC M.A.304 - Data for modifications and repairs','@5- ' UNION ALL

SELECT '1096','AMC M.A.305 - Aircraft continuing airworthiness record system','@5- ' UNION ALL

SELECT '1097','AMC M.A.306 - Operator''s technical log system','@5- ' UNION ALL

SELECT '1098','AMC M.A.307 - Transfer of aircraft continuing airworthiness records','@5- ' UNION ALL

SELECT '1099','Subpart D - Maintenance Standards','@4- ' UNION ALL

SELECT '1100','AMC M.A.401 - Maintenance data','@5- ' UNION ALL

SELECT '1101','AMC M.A.402 - Performance of maintenance','@5- ' UNION ALL

SELECT '1102','AMC M.A.403 - Aircraft defects','@5- ' UNION ALL

SELECT '1103','Subpart E - Components','@4- ' UNION ALL

SELECT '1104','AMC M.A.501 - Installation','@5- ' UNION ALL

SELECT '1107','AMC M.A.504 - Control of unserviceable components','@5- ' UNION ALL

SELECT '1108','Subpart F - Maintenance organisation','@4- ' UNION ALL

SELECT '1109','AMC M.A.601 - Scope','@5- ' UNION ALL

SELECT '1110','AMC M.A.602 - Application','@5- ' UNION ALL

SELECT '1111','AMC M.A.603 - Extent of approval','@5- ' UNION ALL

SELECT '1112','AMC M.A.604 - Maintenance organisation manual','@5- ' UNION ALL

SELECT '1113','AMC M.A.605 - Facilities','@5- ' UNION ALL

SELECT '1114','AMC M.A.606 - Personnel requirements','@5- ' UNION ALL

SELECT '1115','AMC M.A.607 - Certifying staff','@5- ' UNION ALL

SELECT '1802','Appendix IX to EASA Form 2','@3- ' UNION ALL

SELECT '1804','Appendix XI to AMC to M.A.708(c)','@3- ' UNION ALL

SELECT '1808','Section B Procedure for Competent Authorities','@3- ' UNION ALL

SELECT '1116','AMC M.A.608 - Components, equipment and tools','@5- ' UNION ALL

SELECT '1117','AMC M.A.609 - Maintenance data','@5- ' UNION ALL

SELECT '1121','AMC M.A.613 - Component certificate of release to service','@5- ' UNION ALL

SELECT '1122','AMC M.A.614 - Maintenance records','@5- ' UNION ALL

SELECT '1124','AMC M.A.616 - Organisational review','@5- ' UNION ALL

SELECT '1125','AMC M.A.617 - Changes to the approved maintenance organisation','@5- ' UNION ALL

SELECT '1128','Subpart G - Continuing Airworthiness Management Organisation','@4- ' UNION ALL

SELECT '1132','AMC M.A.704 - Continuing airworthiness management exposition','@5- ' UNION ALL

SELECT '1133','AMC M.A.705 - Facilities','@5- ' UNION ALL

SELECT '1134','AMC M.A.706 - Personnel requirements','@5- ' UNION ALL

SELECT '1135','AMC M.A.707 - Airworthiness review staff','@5- ' UNION ALL

SELECT '1136','AMC M.A.708 - Continuing airworthiness management','@5- ' UNION ALL

SELECT '1138','AMC M.A.710 - Airworthiness review','@5- ' UNION ALL

SELECT '1139','AMC M.A.711 - Privileges of the organisation','@5- ' UNION ALL

SELECT '1140','AMC M.A.712 - Quality system','@5- ' UNION ALL

SELECT '1141','AMC M.A.713 - Changes to the approved continuing airworthiness organisation','@5- ' UNION ALL

SELECT '1142','AMC M.A.714 - Record-keeping','@5- ' UNION ALL

SELECT '1145','Subpart H - Certificate of release to service - CRS','@4- ' UNION ALL

SELECT '1146','AMC M.A.801 - Aircraft certificate of release to service','@5- ' UNION ALL

SELECT '1147','AMC M.A.802 - Component certificate of release to service','@5- ' UNION ALL

SELECT '1148','AMC M.A.803 - Pilot-owner authorisation','@5- ' UNION ALL

SELECT '1149','Subpart I - Airworthiness review certificate','@4- ' UNION ALL

SELECT '1150','AMC M.A.901 - Aircraft airworthiness review','@5- ' UNION ALL

SELECT '1152','AMC M.A.903 - Transfer of aircraft registration within the EU','@5- ' UNION ALL

SELECT '1153','AMC M.A.904 - Airworthiness review of aircraft imported into the EU','@5- ' UNION ALL

SELECT '1155','Subpart A - General','@4- ' UNION ALL

SELECT '1157','AMC M.B.102 - Competent authority','@5- ' UNION ALL

SELECT '1159','AMC M.B.104 - Record-keeping','@5- ' UNION ALL

SELECT '1160','AMC M.B.105 - Mutual exchange of information','@5- ' UNION ALL

SELECT '1161','Subpart B - Accountability','@4- ' UNION ALL

SELECT '1163','Subpart C - Continuing Airworthiness','@4- ' UNION ALL

SELECT '1165','AMC M.B.301 - Maintenance programme','@5- ' UNION ALL

SELECT '1805','Appendix X to AMC EASA Form 4','@3- ' UNION ALL

SELECT '1807','Section A Technical Requirements','@3- ' UNION ALL

SELECT '1809','Section A','@3- ' UNION ALL

SELECT '1814','Appendix IV - EASA Form 12','@3- ' UNION ALL

SELECT '1819','Appendix I','@3- ' UNION ALL

SELECT '1820','Appendix II','@3- ' UNION ALL

SELECT '1821','Appendix III','@3- ' UNION ALL

SELECT '1166','AMC M.B.303 - Aircraft continuing airworthiness monitoring','@5- ' UNION ALL

SELECT '1168','Subpart D - Maintenance standards','@4- ' UNION ALL

SELECT '1169','Subpart E - Components','@4- ' UNION ALL

SELECT '1170','Subpart F - Maintenance organisation','@4- ' UNION ALL

SELECT '1172','AMC M.B.602 - Initial approval','@5- ' UNION ALL

SELECT '1173','AMC M.B.603 - Issue of approval','@5- ' UNION ALL

SELECT '1174','AMC M.B.604 - Continuing oversight','@5- ' UNION ALL

SELECT '1175','AMC M.B.605 - Findings','@5- ' UNION ALL

SELECT '1176','AMC M.B.606 - Changes','@5- ' UNION ALL

SELECT '1178','Subpart G - Continuing Airworthiness Management Organisation','@4- ' UNION ALL

SELECT '1179','AMC M.B.701 - Application','@5- ' UNION ALL

SELECT '1180','AMC M.B.702 - Initial approval','@5- ' UNION ALL

SELECT '1181','AMC M.B.703 - Issue of approval','@5- ' UNION ALL

SELECT '1182','AMC M.B.704 - Continuing oversight','@5- ' UNION ALL

SELECT '1183','AMC M.B.705 Findings','@5- ' UNION ALL

SELECT '1184','AMC M.B.706 - Changes','@5- ' UNION ALL

SELECT '1186','Subpart H - Certificate of release to servivce - CRS','@4- ' UNION ALL

SELECT '1187','Subpart I - Airworthiness review certificate','@4- ' UNION ALL

SELECT '1188','AMC M.B.901 - Assessment of recommendations','@5- ' UNION ALL

SELECT '1189','AMC M.B.902 - Airworthiness review by the competent authority','@5- ' UNION ALL

SELECT '1191','AMC 145.A.10 - Scope','@4- ' UNION ALL

SELECT '1815','AMC M.A.708 (b)(3) - Continuing Airworthiness Management ','@5- ' UNION ALL

SELECT '1817','Appendix XII to AMC to M.A.706 (f) and M.B.102 (c)','@3- ' UNION ALL

SELECT '1818','AMC 145.A.65 (b) (3) - Safety and quality policy, maintenance procedures and quality system','@4- ' UNION ALL

SELECT '1292','AMC 66.B.305 - Conversion report for national qualifications','@4- ' UNION ALL

SELECT '1293','AMC 66.B.310 - Conversion report for approved maintenance organisation authorisation','@4- ' UNION ALL

SELECT '1299','Appendix I - Aircraft type ratings for Part-66 Aircraft Maintenance Licence','@3- ' UNION ALL

SELECT '1300','Appendix II - Aicraft Type Practical Experience - List of tasks','@3- ' UNION ALL

SELECT '1305','Section A','@3- ' UNION ALL

SELECT '1310','GM 66.A.20 - Privileges','@4- ' UNION ALL

SELECT '1311','GM 66.A.25 - Basic knowledge requirements','@4- ' UNION ALL

SELECT '1312','GM 66.A.30 - Experience requirements','@4- ' UNION ALL

SELECT '1313','GM 66.A.40 - Continued validity of the aircraft maintenance licence','@4- ' UNION ALL

SELECT '1314','GM 66.A.45 - Type/task training and ratings','@4- ' UNION ALL

SELECT '1822','Appendix IV to AMC to 145.A.30 (e) and 145.B.10 (3)',' ' UNION ALL

SELECT '1824','Commission Regulation (EC) No 1702/2003 of 24 September 2003','@1-' UNION ALL

SELECT '1825','Article 1 - Scope and definitions','@2-' UNION ALL

SELECT '1828','Article 2 - Products, parts and appliances certification','@2-' UNION ALL

SELECT '1829','Article 3 - Design organisations','@2-' UNION ALL

SELECT '1830','Article 4 - Production organisations','@2-' UNION ALL

SELECT '1831','Article 5 - Entry into force','@2-' UNION ALL

SELECT '1832','Annex - PART 21','@2-' UNION ALL

SELECT '1833','21.1 - General','@3-' UNION ALL

SELECT '1834','SECTION A','@3-' UNION ALL

SELECT '1851','Requirements for applicants and acquired rights and obligations','@4 -' UNION ALL

SELECT '1852','Subpart A - General Provisions','@4-' UNION ALL

SELECT '1853','Subpart B - Type-certificates and restricted type-certificates','@4- ' UNION ALL

SELECT '1315','GM 66.A.70 - Conversion provisions','@4- ' UNION ALL

SELECT '1320','Section B - Procedure for Competent Authorities','@3- ' UNION ALL

SELECT '1327','GM 66.B.30 - Exemptions','@4- ' UNION ALL

SELECT '1332','GM 66.B.115 - Procedure for the amendment of an AML to include an aircraft type or group','@4- ' UNION ALL

SELECT '1333','GM 66.B.120 - Procedure for the renewal of an aircraft maintenance licence validity','@4- ' UNION ALL

SELECT '1335','GM 66.B.200 - Examination by the competent authority','@4- ' UNION ALL

SELECT '1854','Subpart C - Not applicable','@4- ' UNION ALL

SELECT '1855','Subpart D - Changes to type-certificates and restricted type-certificates','@4- ' UNION ALL

SELECT '1856','Subpart E - Supplemental type-certificates','@4- ' UNION ALL

SELECT '1857','Subpart F - Production without Production Organisation Approval','@4- ' UNION ALL

SELECT '1858','Subpart G - Production Organisation Approval','@4- ' UNION ALL

SELECT '1859','Subpart H - Airwothiness Certificates','@4- ' UNION ALL

SELECT '1860','Subpart I - Noise Certificates','@4- ' UNION ALL

SELECT '1861','Subpart J - Design Organisation Approval','@4- ' UNION ALL

SELECT '1862','Subpart K - Parts and Appliances','@4- ' UNION ALL

SELECT '1863','Subpart L - Not applicable','@4- ' UNION ALL

SELECT '1864','Subpart M - Repairs','@4- ' UNION ALL

SELECT '1865','Subpart N - Not applicable','@4- ' UNION ALL

SELECT '1866','Subpart O - European Technical Standard Order Authorisation','@4- ' UNION ALL

SELECT '1867','Subpart P ','@4- ' UNION ALL

SELECT '1868','Subpart Q - Identification of products, parts and appliances','@4- ' UNION ALL

SELECT '1869','SECTION B','@3-' UNION ALL

SELECT '1870','Procedures for competent authorities','@4- ' UNION ALL

SELECT '1351','Section A','@3- ' UNION ALL

SELECT '1352','Section B - Procedure for Competent Authority','@3- ' UNION ALL

SELECT '1358','AMC 147.A.100 - Facility requirements','@4- ' UNION ALL

SELECT '1359','AMC 147.A.105 - Personnel requirements','@4- ' UNION ALL

SELECT '1361','AMC 147.A.115 - Instructional equipment','@4- ' UNION ALL

SELECT '1362','AMC 147.A.120 - Maintenance training material','@4- ' UNION ALL

SELECT '1364','AMC 147. A.130 - Training procedures and quality system','@4- ' UNION ALL

SELECT '1365','AMC 147.A.135 - Examinations','@4- ' UNION ALL

SELECT '1366','AMC 147.A.140 - Maintenance training organisation exposition','@4- ' UNION ALL

SELECT '1367','AMC 147.A.145 - Privileges of the maintenance training organisation','@4- ' UNION ALL

SELECT '1372','AMC 147.A.200 - The approved basic training course','@4- ' UNION ALL

SELECT '1373','AMC 147.A.205 - Basic knowledge examinations','@4- ' UNION ALL

SELECT '1374','AMC 147.A.210 - Basic practical assessment','@4- ' UNION ALL

SELECT '1376','AMC 147.A.300 - Aircraft type/task training','@4- ' UNION ALL

SELECT '1380','AMC 147.B.10 - Competent Authority','@4- ' UNION ALL

SELECT '1382','AMC 147.B.20 - Record-keeping','@4- ' UNION ALL

SELECT '1871','Subpart A - General provisions','@4- ' UNION ALL

SELECT '1872','Subpart B - Type-certificates and restricted type-certificates','@4- ' UNION ALL

SELECT '1873','Subpart C - Not applicable','@4- ' UNION ALL

SELECT '1874','Subpart D - Changes to type-certificates and restricted type-certificates','@4- ' UNION ALL

SELECT '1875','Subpart E - Supplemental type-certificates','@4- ' UNION ALL

SELECT '1876','Subpart F - Production without Production Organisation Approval','@4- ' UNION ALL

SELECT '1877','Subpart G - Production Organisation Approval','@4- ' UNION ALL

SELECT '1878','Subpart H - Airworthiness Certificates','@4- ' UNION ALL

SELECT '1879','Subpart I - Noise Certificates','@4- ' UNION ALL

SELECT '1880','Subpart J - Design Organisation Approval','@4- ' UNION ALL

SELECT '1881','Subpart K - Parts and Appliances','@4- ' UNION ALL

SELECT '1387','AMC 147.B.110 - Approval procedure','@4- ' UNION ALL

SELECT '1391','AMC 147.B.130 - Findings','@4- ' UNION ALL

SELECT '1394','Appendix I - Maintenance training organisation exposition (MTOE)','@3- ' UNION ALL

SELECT '1395','Appendix II - EASA Form 4','@3- ' UNION ALL

SELECT '1396','Appendix III - EASA Form 22','@3- ' UNION ALL

SELECT '1398','Section A','@3- ' UNION ALL

SELECT '1399','Section B - Procedure for Competent Authorities','@3- ' UNION ALL

SELECT '1402','GM 147.A.10 - General','@4- ' UNION ALL

SELECT '1405','GM 147.A.100 - Facility requirements','@4- ' UNION ALL

SELECT '1406','GM 147.A.105 - Personnel requirements','@4- ' UNION ALL

SELECT '1407','GM 147.A.110 - Records of instructors, examiners and assessors','@4- ' UNION ALL

SELECT '1408','GM 147.A.115 - Instructional equipment','@4- ' UNION ALL

SELECT '1882','Subpart L - Not applicable','@4- ' UNION ALL

SELECT '1883','Subpart M - Repairs','@4- ' UNION ALL

SELECT '1884','Subpart N - Not applicable','@4- ' UNION ALL

SELECT '1885','Subpart O - European Technical Standard Order Authorisations','@4- ' UNION ALL

SELECT '1886','Subpart P','@4- ' UNION ALL

SELECT '1887','Subpart Q - Identification of Products, Parts and Appliences','@4- ' UNION ALL

SELECT '1888','APPENDICES - EASA Forms','@3-' UNION ALL

SELECT '1889','Appendix I - EASA Form 1 Authorised release Certificate','@4- ' UNION ALL

SELECT '1890','Appendix II - EASA Form 15a Airworthiness Review Certificate','@4- ' UNION ALL

SELECT '1891','Appendix III - EASA Form 20 - Permit to Fly','@4- ' UNION ALL

SELECT '1892','Appendix IV - EASA Form 24 - Restricted Certificate of Airworthiness','@4- ' UNION ALL

SELECT '1893','Appendix V - EASA Form 25 Certificate of Airworthiness','@4- ' UNION ALL

SELECT '1411','GM 147. A.130 - Training procedures and quality system','@4- ' UNION ALL

SELECT '1412','GM 147.A.135 - Examinations','@4- ' UNION ALL

SELECT '1414','GM 147.A.145 - Privileges of the maintenance training organisation','@4- ' UNION ALL

SELECT '1432','GM 147.B.100 - General','@4- ' UNION ALL

SELECT '1434','GM 147.B.110 - Approval procedure','@4- ' UNION ALL

SELECT '1435','GM 147.B.115 - Variation procedure','@4- ' UNION ALL

SELECT '1894','Appendix VI - EASA Form 45 - Noise Certificate','@4- ' UNION ALL

SELECT '1895','Appendix VII - EASA Form 52 - Aircraft Statement of Conformity','@4- ' UNION ALL

SELECT '1896','Appendix VIII - EASA Form 53 - Certificate of Release to Service','@4- ' UNION ALL

SELECT '1897','Appendix IX - EASA Form 55 - Production Organisation Approval Certificate','@4- ' UNION ALL

SELECT '1898','Appendix X - EASA Form 65 - Letter of Agreemnet [Production without POA]','@4- ' UNION ALL

SELECT '1900','Commission Regulation (EC) No 375/2007 of 30 March 2007','@1-' UNION ALL

SELECT '1901','Article 1','@2- ' UNION ALL

SELECT '1902','Article 2','@2- ' UNION ALL

SELECT '1903','Annex','@2- ' UNION ALL

SELECT '1905','1','@3- ' UNION ALL

SELECT '1906','2','@3- ' UNION ALL

SELECT '1907','3','@3- ' UNION ALL

SELECT '1908','4','@3- ' UNION ALL

SELECT '1909','5','@3- ' UNION ALL

SELECT '1910','6','@3- ' UNION ALL

SELECT '1911','7','@3- ' UNION ALL

SELECT '1912','8','@3- ' UNION ALL

SELECT '1913','9','@3- '

Sean Lange SSC Guru Points: 286609 More actions · Answer 5

OK so now we have ddl and sample data. What do you want for output? Your original description is very unclear.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Hadrian Hall of Fame Points: 3742 More actions · Answer 6

I have to insert into a excel file the questions and question pool level (which come from alttext colum -@1-.@2-,@3-.@4-.@5-)

questionID Question Pool Level 1 Question Pool Level 2 Question Pool Level 3 Question Pool Level 4 Question Pool Level 5

xxx Level 1

xxy Level 1

xxt Level 1 Level 2

xxc Level 1 Level 2

xtz Level 1 Level 2 Level 3

xrt Level 1 Level 2 Level 3

xty Level 1 Level 2 Level 3 Level 4

xmt Level 1 Level 2 Level 3 Level 4

etc Level 1 Level 2 Level 3 Level 4 Level 5

etcs Level 1 Level 2 Level 3 Level 4 Level 5

Level 1 Level 2 Level 3 Level 4 Level 5

This is what I received from the customer:

When the question is assigned to the Level 1 (@1) then level 1 should be described only.

When the question is assigned to the Level 2 (@2) then level 1 and 2 should be described.

When the question is assigned to the Level 3 (@3) then level 1, 2, and 3 should be described.

When the question is assigned to the Level 4 (@4) then level 1, 2, 3 and 4 should be described.

When the question is assigned to the Level 5 (@5) then level 1, 2, 3, 4, and 5 should be described.

I have to extract all question pool levels @1, @2, @3, @4, and @5 assigned to each question.

Hope this info will help you!

Sean Lange SSC Guru Points: 286609 More actions · Answer 7

Hadrian (12/20/2011)
I have to insert into a excel file the questions and question pool level (which come from alttext colum -@1-.@2-,@3-.@4-.@5-)
questionID Question Pool Level 1 Question Pool Level 2 Question Pool Level 3 Question Pool Level 4 Question Pool Level 5
xxx Level 1
xxy Level 1
xxt Level 1 Level 2
xxc Level 1 Level 2
xtz Level 1 Level 2 Level 3
xrt Level 1 Level 2 Level 3
xty Level 1 Level 2 Level 3 Level 4
xmt Level 1 Level 2 Level 3 Level 4
etc Level 1 Level 2 Level 3 Level 4 Level 5
etcs Level 1 Level 2 Level 3 Level 4 Level 5
Level 1 Level 2 Level 3 Level 4 Level 5
This is what I received from the customer:
When the question is assigned to the Level 1 (@1) then level 1 should be described only.
When the question is assigned to the Level 2 (@2) then level 1 and 2 should be described.
When the question is assigned to the Level 3 (@3) then level 1, 2, and 3 should be described.
When the question is assigned to the Level 4 (@4) then level 1, 2, 3 and 4 should be described.
When the question is assigned to the Level 5 (@5) then level 1, 2, 3, 4, and 5 should be described.
I have to extract all question pool levels @1, @2, @3, @4, and @5 assigned to each question.
Hope this info will help you!

Well I certainly can't make heads or tails of what you want. The best I could possibly come up with is that you want to pivot your data from #structure1. The description of the desired output and the sample data just don't match. You have a questions table that has two columns. The very first question is structure 35. There are two more questions with the same structure but there is no structure data.

I will be happy to help you but you have to explain clearly what you want as output from this sample data.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

drew.allen SSC Guru Points: 77013 More actions · Answer 8

The problem is that your hierarchy is improperly modeled. Your structure is incomplete. You have node data and depth data, but you don't have the information about how one node is connected to any other node. For instance, Subpart D - Maintenance Standards is obviously the parent of the other three listed below, but there is no way for SQL Server to know that unless you tell it. You can't rely on the structureID being sequential, because that's not guaranteed.

structureID Text altText

1099 Subpart D - Maintenance Standards @4-

1100 AMC M.A.401 - Maintenance data @5-

1101 AMC M.A.402 - Performance of maintenance @5-

1102 AMC M.A.403 - Aircraft defects @5-

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA