Cube in SQLserver: multi-dimensional dataset instance details, sqlservercube

Cube in SQLserver: multi-dimensional dataset instance details, sqlservercube

1. cube: generate a multi-dimensional dataset that contains a cross table that may be combined by different dimensions. Use the with keyword to connect to the with cube.

Use union all join as needed

Determines whether the null value of a column comes from the source data or the GROUPING keyword is used by the cube.

GROUPING ([file number]) = 1: null value from cube (representing all file numbers)
GROUPING ([file number]) = 0: null value from source data

Example:

SELECT * INTO # GETFROM (SELECT * FROM (select case when (GROUPING ([file number]) = 1) then' total 'else [file number] end as 'file number ', case when (GROUPING ([series]) = 1) then' total 'else [series] end as 'series', case when (GROUPING ([Manager]) = 1) THEN 'total 'else [store manager] end as 'shop Post', SUM (number of remaining) AS 'total left', case when (GROUPING ([store name]) = 1) then' total 'else [store name] end as 'shop name' FROM # PudianCard group by [file number], [store name], [store manager], [series] WITH cube HAVING GROUPI NG ([store name])! = 1 and grouping ([file number]) = 1 -- and grouping ([series]) = 1) as m union all (SELECT * FROM (select case when (GROUPING ([file number]) = 1) then' total 'else [file number] end as 'file number ', case when (GROUPING ([series]) = 1) then' total 'else [series] end as 'series', case when (GROUPING ([Manager]) = 1) THEN 'total 'else [store manager] end as 'shop Post', SUM (number of remaining) AS 'total left', case when (GROUPING ([store name]) = 1) then' total 'else [store name] end as 'shop name' FROM # PudianCard group [File number], [store name], [store manager], [series] WITH cube having grouping ([store name])! = 1 and grouping ([Manager])! = 1) as p) union all (SELECT * FROM (select case when (GROUPING ([file number]) = 1) then' total 'else [file number] end as 'file number ', case when (GROUPING ([series]) = 1) then' total 'else [series] end as 'series', case when (GROUPING ([Manager]) = 1) THEN 'total 'else [store manager] end as 'shop Post', SUM (number of remaining) AS 'total left', case when (GROUPING ([store name]) = 1) then' total 'else [store name] end as 'shop name' FROM # PudianCard group by [file number], [store name], [store manager], [series] WITH cube HAVING GROUPIN G ([store name])! = 1 and grouping ([Manager])! = 1) as w) union all (SELECT * FROM (select case when (GROUPING ([file number]) = 1) then' total 'else [file number] end as 'file number ', case when (GROUPING ([series]) = 1) then' total 'else [series] end as 'series', case when (GROUPING ([Manager]) = 1) THEN 'total 'else [store manager] end as 'shop Post', SUM (number of remaining) AS 'total left', case when (GROUPING ([store name]) = 1) then' total 'else [store name] end as 'shop name' FROM # PudianCard group by [file number], [store name], [store manager], [series] WITH cube having grouping ([store name]) = 1 and grouping ([store manager]) = 1 and grouping ([file number]) = 1) as k) AS T

2. rollup: similar to cube

3. Use the data of a column as the column name, dynamically load the data, and splice strings using stored procedures.

DECLARE @ st nvarchar (MAX) = ''; SELECT @ st = @ st + 'max (case when [series] = ''' + CAST ([series] as varchar) + '''then [total remaining] else null end) as ['+ CAST ([series] as varchar) +'], 'From # getgroup by [series]; print @ st;

4. Create tables by grouping a column

SELECT 'select ROW_NUMBER () over (order by [card item] desc) as [number], [member], [file number], [card item], [remaining times], [employee], [store name] into '+ ltrim ([store name]) + 'from query where [store name] = ''' + [store name] + ''' order by [card item] desc' from query group by [store name]

Summary

The above is all the details about cube in SQLserver: multi-dimensional dataset instances. I hope it will be helpful to you. For more information, see: MYSQL subquery and nested query optimization instance parsing, several important MySQL variables, and analysis of technical points of oracle SQL statement optimization, if you have any questions, you can leave a message at any time. The editor will reply to you in a timely manner. Thank you for your support!