我有两个表:
SELECT *
INTO #tblVariety
FROM (VALUES
('DT001', 'Veg', '2016-09-30'),
('DT002', 'Non-Veg', '2016-09-30'),
('DT003', 'Coffee', '2016-09-30'),
('DT004', 'Cakes', '2016-09-30')
) as t(DID, Dishtype, AddedDate)
SELECT *
INTO #tblProducts
FROM (VALUES
('MN001', 'Dosa', 'South indian famous food', 60, 'Veg'),
('MN002', 'Meals', 'Rice and chenna fry and more indian culture', 150, 'Veg'),
('MN003', 'Burger', 'Its chines made culture and fastfoods', 80, 'Veg'),
('MN004', 'Chettinadu chicken', 'Its very tasty and spicy', 100, 'Non-Veg'),
('MN005', 'chicken Loipop', 'this chicken leg piece fry', 120, 'Non-Veg'),
('MN006', 'Grill chicken', 'its very fry in grill machines', 350, 'Non-Veg'),
('MN007', 'choco cofee', 'its hard choco and sweetest without milk', 100, 'Coffee'),
('MN008', 'Mintmojiti', 'mint tast its feel fresh', 100, 'Coffee'),
('MN009', 'Straberry Milkshake', 'Its healthy and sweetest items', 150, 'Coffee'),
('MN010', 'Home Strawberry', 'its a sweetest and tasty', 90, 'Coffee'),
('MN011', 'Chocolate cake', 'its a dark choco and soft emmy', 300, 'Cakes'),
('MN012', 'Mary Berry', 'its formal and crunchi cake', 200, 'Cakes'),
('MN013', 'Buttar', 'confetti its a milk mixed made cake', 150, 'Cakes')
) as t(ProId, ProductName, ProductDescription, ProductPrice, VNV)
我想输出:
Veg
Dosa Meals Burger
South indian famous food Rice and chenna fry and more indian culture cheinese ffood
60 150 80
Non-Veg
Chettinadu chicken
Its very tasty and spicy
100
Cofee
(values)...................................
Cakes
(Values).............................................
谁来帮帮我
一种方法是使用透视:
;WITH cte AS (
SELECT *
FROM (
SELECT *
FROM (
SELECT ProId,
VNV as Dishtype,
CAST(VNV as nvarchar(max)) as Product,
CAST(ProductName as nvarchar(max)) as ProductName,
CAST(ProductDescription as nvarchar(max)) as ProductDescription,
CAST(ProductPrice as nvarchar(max)) as ProductPrice,
ROW_NUMBER() OVER (PARTITION BY VNV ORDER BY ProId) as seq
FROM #tblProducts
) as m
UNPIVOT (
[Values] FOR [Columns] IN (Product, ProductName, ProductDescription, ProductPrice)
) as unpvt
) as d
)
SELECT c.[Values],
c1.[Values],
c2.[Values]
FROM cte c
LEFT JOIN cte c1
ON c.[Columns] = c1.[Columns] and c1.seq = c.seq+1 AND c.Dishtype=c1.Dishtype
LEFT JOIN cte c2
ON c1.[Columns] = c2.[Columns] and c2.seq = c1.seq+1 AND c1.Dishtype=c2.Dishtype
WHERE c.seq IN (1,4,7,10)
Values |Values |Values
-------------------------------------------+-------------------------------------------+-------------------------------------------
Cakes Cakes Cakes
Chocolate cake Mary Berry Buttar
its a dark choco and soft emmy its formal and crunchi cake confetti its a milk mixed made cake
300 200 150
Coffee Coffee Coffee
choco cofee Mintmojiti Straberry Milkshake
its hard choco and sweetest without milk mint tast its feel fresh Its healthy and sweetest items
100 100 150
Coffee NULL NULL
Home Strawberry NULL NULL
its a sweetest and tasty NULL NULL
90 NULL NULL
Non-Veg Non-Veg Non-Veg
Chettinadu chicken chicken Loipop Grill chicken
Its very tasty and spicy this chicken leg piece fry its very fry in grill machines
100 120 350
Veg Veg Veg
Dosa Meals Burger
South indian famous food Rice and chenna fry and more indian culture Its chines made culture and fastfoods
60 150 80
另一种方法是将所有菜单转换为XML格式，然后使用XSLT或其他方法将其转换为所需的格式:
SELECT t.VNV '@type',
CAST((
SELECT ProId '@prodid',
ProductName '@prodname',
ProductDescription '@proddesc',
ProductPrice '@prodprice'
FROM #tblProducts
WHERE t.VNV = VNV
FOR XML PATH('dish')
) as xml) as proddetails
FROM #tblProducts t
GROUP BY t.VNV
FOR XML PATH('dish')
<dish type="Cakes">
<proddetails>
<dish prodid="MN011" prodname="Chocolate cake" proddesc="its a dark choco and soft emmy" prodprice="300" />
<dish prodid="MN012" prodname="Mary Berry" proddesc="its formal and crunchi cake" prodprice="200" />
<dish prodid="MN013" prodname="Buttar" proddesc="confetti its a milk mixed made cake" prodprice="150" />
</proddetails>
</dish>
<dish type="Coffee">
<proddetails>
<dish prodid="MN007" prodname="choco cofee" proddesc="its hard choco and sweetest without milk" prodprice="100" />
<dish prodid="MN008" prodname="Mintmojiti" proddesc="mint tast its feel fresh" prodprice="100" />
<dish prodid="MN009" prodname="Straberry Milkshake" proddesc="Its healthy and sweetest items" prodprice="150" />
<dish prodid="MN010" prodname="Home Strawberry" proddesc="its a sweetest and tasty" prodprice="90" />
</proddetails>
</dish>
<dish type="Non-Veg">
<proddetails>
<dish prodid="MN004" prodname="Chettinadu chicken" proddesc="Its very tasty and spicy" prodprice="100" />
<dish prodid="MN005" prodname="chicken Loipop" proddesc="this chicken leg piece fry" prodprice="120" />
<dish prodid="MN006" prodname="Grill chicken" proddesc="its very fry in grill machines" prodprice="350" />
</proddetails>
</dish>
<dish type="Veg">
<proddetails>
<dish prodid="MN001" prodname="Dosa" proddesc="South indian famous food" prodprice="60" />
<dish prodid="MN002" prodname="Meals" proddesc="Rice and chenna fry and more indian culture" prodprice="150" />
<dish prodid="MN003" prodname="Burger" proddesc="Its chines made culture and fastfoods" prodprice="80" />
</proddetails>
</dish>