Compare classic SQL Row to column + grouping set

This is a more classic row-to-column. With fewer SQL statements, you can implement row-to-column. But this row-to-column needs to be developed two times according to your business needs, customized and or rewritten according to your needs.

Let's talk about this in a simple way.

1. Build a table Script

CREATE TABLE [dbo]. [Table_1] (
[a] [int] not NULL,--destination column name
[b] [uniqueidentifier] not NULL,--Target data
[c] [nchar] (Ten) not null--filter conditions
) on [PRIMARY]

GO

ALTER TABLE [dbo]. [Table_1] ADD CONSTRAINT [Df_table_1_b] DEFAULT (NEWID ()) for [b]

2. Build a number of scripts

INSERT [dbo]. [Table_1] ([a], [b], [C]) VALUES (n ' f01ac2e9-b5bc-4993-926c-9216203db3f1 ', n ' language ')
INSERT [dbo]. [Table_1] ([a], [b], [C]) VALUES (6C2D753B-36A7-468A-BBF7-4BDA8BEEE4DF, n ' ", N ' mathematics ')
INSERT [dbo]. [Table_1] ([a], [b], [C]) VALUES (n ' 6093ecd4-7b5b-4225-a6d3-dec213f14a6b ', n ' study-study ')
INSERT [dbo]. [Table_1] ([a], [b], [C]) VALUES (n ' 6961f7f5-a2b0-45b7-8235-305ad22c2295 ', n ' language ')
INSERT [dbo]. [Table_1] ([a], [b], [C]) VALUES (n ' efdf5e97-4fd9-4c05-bbc0-6f18b38bf2d0 ', n ' language ')
INSERT [dbo]. [Table_1] ([a], [b], [C]) VALUES (n ' 116d448d-2cd8-4fdf-a7d8-c8f4f53e8e05 ', n ' math ')
INSERT [dbo]. [Table_1] ([a], [b], [C]) VALUES (n ' cf33dbc1-c265-4835-bb17-f684893ad96c ', n ' study-study ')
INSERT [dbo]. [Table_1] ([a], [b], [C]) VALUES (n ' f835c374-4fef-4fd9-8a5b-e16f5feb6bda ', n ' math ')
INSERT [dbo]. [Table_1] ([a], [b], [C]) VALUES (n ' fb0ce23f-a2bc-4d65-82d3-fb137f414fcf ', n ' study-study ')

3. Row-to-column implementation-Get ' language ' class row to column data

declare @0 nvarchar (4000)---Define the dynamic SQL length (nvarchar default maximum length is 4000)
Set @0= ' select ' Row2col ' as Demo, ' Test categorical data ' as CallBack ';--splicing head
--set @0= ' select ' ' as A ';
With and other as (select a, from table_1 where c= ' language ')--structured fetch data source
Select @[email protected]+ ', [' +cast ([a] as nvarchar (p)) + ']= ' ' +cast ([b] as nvarchar (+)) + ' ' from the other--pagination dynamic stitching core statement
Execute sp_executesql @0--Perform dynamic stitching of SQL

Execution results such as

Figure 1

Visible from the graph, more than 2 columns, respectively, "Demo", "CallBack".

Why do we have to make 2 more columns? Because this execution is based on the collection dynamic stitching, the SQL statement begins with a comma (,). To ensure that SQL is executable, at least one column of data must be appended.

The reason I'm appending 2 columns here is because in addition to identifying this as a dynamic column result set, a static value callback is performed based on the business of data stitching. Used to determine the attribution classification of the current data. My callback column.

You can tell me clearly that this data is "Test classification data", after I get the data, I want to filter the value two times, and dynamically build the cache business table, and the classification mapping table. Of course, for you, it can be negligible.

Is there a repeating column in the data? There are repeating columns in the data, and I'm looking for 12, which will result in a duplicate name or ambiguous target. Well, I can be very responsible for saying that if that's the result you want, then you're on your

Business execution, not yet understood well. Our line-to-row execution targets, in addition to turning the data around, match the response business. When you do this, reconsider whether there is a business score, a data special case, or a business rule score

You missed it?

declare @0 nvarchar (4000)
Set @0= ' select ' Row2col ' as Demo, ' Test categorical data ' as CallBack ';
--set @0= ' select ' ' as A ';
With and other as (
Select a b from Table_1
Where c= ' study in the classroom '
)
Select @[email protected]+ ', [' +cast ([a] as nvarchar (+)) + ']= ' +cast ([b] as nvarchar (+)) + "' from the other
Execute sp_executesql @0

Figure 2

Is the final execution target of the row-to-column. That is, the data is clear, the column name is clear, the business is clear.

4. Constraints

It can be seen through the above 2 columns. Although our data source is 3 columns, it is necessary and only possible to use 2 columns from the data source in the actual build, it is impossible to display multiple columns in the data source, and to use row to column, it is ideal to query the result set of "self study". When you appear similar to Figure 1 o'clock, you have to stop to consider whether the data source is available? Do you need to refine your data further? A business analysis is missing? Or is there redundancy in the data?

5. Improve

When I want to get all the rows and columns of data, comment out the where in the other structure block where to try?

When I want to do a grouping, my current data is not enough. Then re-create the data first, the script is as follows:

DELETE from [dbo]. [table_1]--clears the original data and constructs the data that can perform the grouping set
INSERT [dbo]. [Table_1] ([a], [b], [C]) VALUES (n ' f01ac2e9-b5bc-4993-926c-9216203db3f1 ', n ' language ')
INSERT [dbo]. [Table_1] ([a], [b], [C]) VALUES (n ' 6c2d753b-36a7-468a-bbf7-4bda8beee4df ', n ' math ')
INSERT [dbo]. [Table_1] ([a], [b], [C]) VALUES (n ' 6093ecd4-7b5b-4225-a6d3-dec213f14a6b ', n ' study-study ')
INSERT [dbo]. [Table_1] ([a], [b], [C]) VALUES (n ' 6961f7f5-a2b0-45b7-8235-305ad22c2295 ', n ' language ')
INSERT [dbo]. [Table_1] ([a], [b], [C]) VALUES (n ' efdf5e97-4fd9-4c05-bbc0-6f18b38bf2d0 ', n ' language ')
INSERT [dbo]. [Table_1] ([a], [b], [C]) VALUES (n ' 116d448d-2cd8-4fdf-a7d8-c8f4f53e8e05 ', n ' math ')
INSERT [dbo]. [Table_1] ([a], [b], [C]) VALUES (n ' cf33dbc1-c265-4835-bb17-f684893ad96c ', n ' study-study ')
INSERT [dbo]. [Table_1] ([a], [b], [C]) VALUES (n ' f835c374-4fef-4fd9-8a5b-e16f5feb6bda ', n ' math ')
INSERT [dbo]. [Table_1] ([a], [b], [C]) VALUES (n ' fb0ce23f-a2bc-4d65-82d3-fb137f414fcf ', n ' study-study ')

Figure 3

, the data is based on the group business form, the data is built, if your data source and I built the data similar, then your data source is the possibility of doing grouping set.

Provide the following ideas:

First, the classification data collection is traversed based on the cursor.

Second, use the row to column above for data conversion.

Finally, the target data is put into the temporary table and returned after the collection.

The results of the final implementation are as follows:

Figure 4

Full of feasibility proof, the data is confidential, so have done the processing. Its feasibility is beyond doubt.

Do the supporting script later, go home first. Work ~~!

Compare classic SQL Row to column + grouping set