1. 1
2. 2
3. 3
4. 4
5. 5
6. 6
7. 7
8. 8
9. 9
10. 10
11. 11
12. 12
13. 13
14. 14
15. 15
16. 16
17. 17
18. 18
19. 19
20. 20
21. 21
22. 22
23. 23
24. 24
25. 25
26. 26
27. 27
28. 28
29. 29
30. 30
31. 31
32. 32
33. 33
34. 34
35. 35
36. 36
37. 37
38. 38
39. 39
40. 40
41. 41
42. 42
43. 43
44. 44
45. 45
46. 46
47. 47
48. 48
49. 49
50. 50
51. 51
52. 52
53. 53
54. 54
55. 55
56. 56
57. 57
58. 58
59. 59
60. 60
61. 61
62. 62
63. 63
64. 64
65. 65
66. 66
67. 67
68. 68
69. 69
70. 70
71. 71
72. 72
73. 73
74. 74
75. 75
76. 76
77. 77
78. 78
79. 79
80. 80
81. 81
82. 82
83. 83
84. 84
85. 85
86. 86
87. 87
88. 88
89. 89
90. 90
91. 91
92. 92
93. 93
94. 94
95. 95
96. 96
97. 97
98. 98
99. 99
100. 100

select x1.g_id,
(select switch(

str='0','новое строительство',
str='1','подготовка территории',
str='2','реконструкция',
str='3','снос' ) 

from t1 
where g_id=x1.p_id 

and f_id in(1,71,81) 
and id=1),

(select switch(
str='0','административное здание',
str='1','детский сад',
str='2','дорога',
str='3','жилое здание',
str='4','инженерные сети',
str='5','коммуникации',
str='6','поликлиника',
str='7','путепровод',
str='8','ЦТП',
str='9','школа') from t1 
where g_id=x1.p_id 
and f_id in(1,71,81) 
and id=2),

(select 'Субъект '+
(select str from subj 
where type1=1 
and id1=int(x3.str))+

switch(
x4.str='0',', город ',
x4.str='1',', деревня ',
x4.str='2',', ПГТ ',
x4.str='3',', поселок ',
x4.str='4',', село ')+

(select str from subj 
where type1=3 
and id1=int(x5.str))+iif(x6.str,

(select ', '+str from subj 
where type1=2 
and id1=int(x6.str)),'')+iif(x7.str,

(select ', '+str from subj 
where type1=5 
and id1=int(x7.str)),'')+iif(x8.str,',микр.'+x8.str,'')+iif(x9.str,', квартал '+x9.str,'')+iif(x10.str,', жил.группа '+x10.str,'')+

(select Format(', '+str) from t1 
where f_id in(1,71,81)
 and id=12 
and g_id=x1.p_id)+iif(x11.str,', вл.'+x11.str,'')+iif(x12.str,', д.'+x12.str,'')+iif(x13.str,', кор.'+x13.str,'')+iif(x14.str,', стр.'+x14.str,'') 

from t1 as x3,t1 as x4,t1 as x5,t1 as x6,t1 as x7,t1 as x8,t1 as x9,t1 as x10,t1 as x11,t1 as x12,t1 as x13,t1 as x14 
where x3.f_id in(1,71,81) 

and x3.id=4 
and x3.g_id=x1.p_id 
and x4.f_id=x3.f_id 
and x4.id=7 
and x4.g_id=x3.g_id 
and x5.f_id=x3.f_id 
and x5.id=8 and x5.g_id=x3.g_id 
and x6.f_id=x3.f_id 
and x6.id=5 
and x6.g_id=x3.g_id 
and x7.f_id=x3.f_id 
and x7.id=6 
and x7.g_id=x3.g_id 
and x8.f_id=x3.f_id 
and x8.id=9 
and x8.g_id=x3.g_id 
and x9.f_id=x3.f_id 
and x9.id=10 
and x9.g_id=x3.g_id 
and x10.f_id=x3.f_id 
and x10.id=11 
and x10.g_id=x3.g_id 
and x11.f_id=x3.f_id 
and x11.id=13 
and x11.g_id=x3.g_id 
and x12.f_id=x3.f_id 
and x12.id=14 
and x12.g_id=x3.g_id 
and x13.f_id=x3.f_id 
and x13.id=15 
and x13.g_id=x3.g_id 
and x14.f_id=x3.f_id 
and x14.id=16 
and x14.g_id=x3.g_id),

(select code1 from fkko,t1
where id1=int(t1.str) 
and g_id=x1.g_id 
and f_id=x1.f_id