Server向下显示数据

Question

我有一张这样的桌子：

CREATE TABLE #Categories (CategoryText VARCHAR(50), CategoryUrl VARCHAR(50), SubCategoryText VARCHAR(50), SubCategoryUrl VARCHAR(50))
INSERT INTO #Categories SELECT 'Lighting', 'http://lighting.com', 'Chandeliers', 'http://chandeliers.com' 
INSERT INTO #Categories SELECT 'Lighting', 'http://lighting.com', 'Lamps', 'http://lamps.com' 
INSERT INTO #Categories SELECT 'Hardware', 'http://hardware.com', 'Hooks', 'http://hooks.com' 
INSERT INTO #Categories SELECT 'Hardware', 'http://hardware.com', 'Hinges', 'http://hinges.com' 

看上去像是：

CategoryText                                       CategoryUrl                                        SubCategoryText                                    SubCategoryUrl
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
Lighting                                           http://lighting.com                                Chandeliers                                        http://chandeliers.com
Lighting                                           http://lighting.com                                Lamps                                              http://lamps.com
Hardware                                           http://hardware.com                                Hooks                                              http://hooks.com
Hardware                                           http://hardware.com                                Hinges                                             http://hinges.com

如何动态显示数据，如下所示：

Type        Text                                               Url
----------- -------------------------------------------------- --------------------------------------------------
Category    Lighting                                           http://lighting.com
SubCategory Chandeliers                                        http://chandeliers.com
SubCategory Lamps                                              http://lamps.com
Category    Hardware                                           http://hardware.com
SubCategory Hinges                                             http://hinges.com
SubCategory Hooks                                              http://hooks.com

我还需要保留正确的类别和子类别的顺序。

我想到了UNPIVOT，但我不知道如何在这里应用它，因为数据结构/需求与大多数示例不同。

任何帮助都是非常感谢的。

Answer 1

我能够做到这一点，保留类别和子类别的顺序。

我使用@SliverNinja的CASE语句来获得正确的类型和文本。

对于排序，我添加了2列CategorySortOrder和SubCategorySortOrder。我将类别和子类别sortorder组合起来，创建了一个新的SortOrder列。然后，我按该列进行分组和排序，以获得我的最终结果。

CREATE TABLE #Categories (CategoryText VARCHAR(50), CategoryUrl VARCHAR(50), CategorySortOrder INT,SubCategoryText VARCHAR(50), SubCategoryUrl VARCHAR(50), SubCategorySortOrder INT)
INSERT INTO #Categories SELECT 'Lighting', 'http://lighting.com', 1, 'Chandeliers', 'http://chandeliers.com',1
INSERT INTO #Categories SELECT 'Lighting', 'http://lighting.com', 1, 'Lamps', 'http://lamps.com' , 2
INSERT INTO #Categories SELECT 'Hardware', 'http://hardware.com', 2 ,'Hooks', 'http://hooks.com' ,1
INSERT INTO #Categories SELECT 'Hardware', 'http://hardware.com', 2,'Hinges', 'http://hinges.com' ,2

SELECT
    Type
    , Text
    , Url
    , MIN(SortOrder)[SortOrder]
INTO 
    #cats
FROM
    (
    SELECT 
        CASE WHEN SubCategoryText IS NULL THEN 'Category' ELSE 'SubCategory' END [Type]
        , CASE WHEN SubCategoryText IS NULL THEN CategoryText ELSE SubCategoryText END [Text]
        , [Url]
        , CASE WHEN SubCategoryText IS NULL THEN CAST(CAST(CategorySortOrder AS VARCHAR) + CAST(SubCategorySortOrder AS VARCHAR) AS NUMERIC)  ELSE CAST(CAST(CategorySortOrder AS VARCHAR) + CAST(SubCategorySortOrder AS VARCHAR) AS NUMERIC) + 1 END[SortOrder]
    FROM
        (
            SELECT
                CategoryText
                , NULL [SubCategoryText]
                , CategoryUrl [Url]
                , CategorySortOrder
                , SubCategorySortOrder
            FROM
                #Categories
            UNION ALL
            SELECT
                CategoryText
                , SubCategoryText
                , SubCategoryUrl
                , CategorySortOrder
                , SubCategorySortOrder
            FROM
                #Categories
        )t
    )t
GROUP BY
    Type
    , Text
    , Url
ORDER BY
    SortOrder 

SELECT 
    Type
    , Text
    , Url
FROM
    #Cats

Answer 2

试试这个：

select distinct
  Type = 'Category',
  Parent = CategoryText,
  Text = CategoryText,
  Url = CategoryUrl
from #Categories
union all
select 
  Type = 'SubCategory',
  Parent = CategoryText,
  Text = SubCategoryText,
  Url = SubCatgoryUrl
from  #Categories
order by Parent,Type,Text

Answer 3

这应该适用于具有排名函数的you...using a UNION .

select CASE WHEN SubCategoryText IS NULL THEN 'Category' ELSE 'SubCategory' END as Type,
        CASE WHEN SubCategoryText IS NULL THEN CategoryText ELSE SubCategoryText END as Type,
        CategoryUrl
from (      
select *, RANK() OVER (ORDER BY CategoryText, SubCategoryText) AS Rank
from (
select CategoryText, null as SubCategoryText, CategoryUrl
from #Categories
union 
select CategoryText, SubCategoryText, SubCategoryUrl
from #Categories
)a)b
order by Rank