Блог

Предположим, у меня есть таблица в базе данных, имеющая 60 столбцов. Может кто-нибудь подсказать мне запрос для получения количества всех отдельных записей в каждом столбце (60) таблицы.

 Cl1     Cl2 Cl3
68722   No  No  No  No  No  Yes Male    No  No
68723   No  Yes No  No  No  Yes FeMale  No  No
68725       No  No  No  No  Yes Male    No  No
  

Как и в приведенной выше таблице, предположим, у меня есть 10 столбцов (от столбца 1 до столбца 10)

Ответ я хочу :

 Cl2 No =2 , Blank=1

Cl3 No=2,  Yes =1 
  

и так далее до 10

Пожалуйста, дайте мне запрос для Sql Server.

ОТКЛЮЧИТЬ СГРУППИРОВАТЬ ПО и ПОДСЧИТАТЬ:

 SELECT  [Columns],
        Case when [Values] = '' THEN 'Blank' ELSE [Values] END [Values],
        COUNT(Id) as HowMany
FROM (
    SELECT  Id,
            CAST(Cl1 as nvarchar(max)) as Cl1,
            CAST(Cl2 as nvarchar(max)) as Cl2,
            CAST(Cl3 as nvarchar(max)) as Cl3,
            CAST(Cl4 as nvarchar(max)) as Cl4,
            CAST(Cl5 as nvarchar(max)) as Cl5,
            CAST(Cl6 as nvarchar(max)) as Cl6,
            CAST(Cl7 as nvarchar(max)) as Cl7,
            CAST(Cl8 as nvarchar(max)) as Cl8,
            CAST(Cl9 as nvarchar(max)) as Cl9
    FROM YourTable
) as t
UNPIVOT (
    [Values] FOR [Columns] IN ( Cl1, Cl2, Cl3, Cl4, Cl5, Cl6, Cl7, Cl8, Cl9)
) as unpvt
GROUP BY [Columns], [Values]
ORDER BY [Columns]
  

Вывод:

 Columns Values  HowMany
Cl1     Blank   1
Cl1     No      2
Cl2     No      2
Cl2     Yes     1
Cl3     No      3
Cl4     No      3
Cl5     No      3
Cl6     Yes     3
Cl7     FeMale  1
Cl7     Male    2
Cl8     No      3
Cl9     No      3
  

Вы можете поместить это в CTE и:

 ;WITH cte as (
    SELECT  [Columns],
            Case when [Values] = '' THEN 'Blank' ELSE [Values] END   '=' CAST(COUNT(Id) as nvarchar(max)) as HowMany
    FROM (
        SELECT  Id,
                CAST(Cl1 as nvarchar(max)) as Cl1,
                CAST(Cl2 as nvarchar(max)) as Cl2,
                CAST(Cl3 as nvarchar(max)) as Cl3,
                CAST(Cl4 as nvarchar(max)) as Cl4,
                CAST(Cl5 as nvarchar(max)) as Cl5,
                CAST(Cl6 as nvarchar(max)) as Cl6,
                CAST(Cl7 as nvarchar(max)) as Cl7,
                CAST(Cl8 as nvarchar(max)) as Cl8,
                CAST(Cl9 as nvarchar(max)) as Cl9
        FROM YourTable
    ) as t
    UNPIVOT (
        [Values] FOR [Columns] IN ( Cl1, Cl2, Cl3, Cl4, Cl5, Cl6, Cl7, Cl8, Cl9)
    ) as unpvt
    GROUP BY [Columns], [Values]
)

SELECT DISTINCT 
            [Columns]  ' '      
            STUFF((
            SELECT ';' HowMany
            FROM cte
            WHERE c.[Columns] = [Columns]
            FOR XML PATH('')
            ),1,1,'') as [Values]
FROM cte c
  

Вывод:

 Values
Cl1 Blank=1;No=2
Cl2 No=2;Yes=1
Cl3 No=3
Cl4 No=3
Cl5 No=3
Cl6 Yes=3
Cl7 FeMale=1;Male=2
Cl8 No=3
Cl9 No=3