Mixing
PIVOT/UNPIVOT more than one column
Is there a way to use SQL Pivot/Unpivot to produce the output under Converted below from the table Original?
sql
sql-server tsql pivot unpivot edited Jan 4 at 16:02
Andrea
7,761144954
asked Jan 2 at 4:01
VTISCHUKVTISCHUK
174
add a comment |
Is there a way to use SQL Pivot/Unpivot to produce the output under Converted below from the table Original?
sql
sql-server tsql pivot unpivot edited Jan 4 at 16:02
Andrea
7,761144954
asked Jan 2 at 4:01
VTISCHUKVTISCHUK
174
2
Please show your code attempt.
– Dale Burrell
Jan 2 at 4:05
add a comment |
Is there a way to use SQL Pivot/Unpivot to produce the output under Converted below from the table Original?
sql
sql-server tsql pivot unpivot edited Jan 4 at 16:02
Andrea
7,761144954
asked Jan 2 at 4:01
VTISCHUKVTISCHUK
174
Is there a way to use SQL Pivot/Unpivot to produce the output under Converted below from the table Original?
sql
sql-server tsql pivot unpivot sql
sql-server tsql pivot unpivot edited Jan 4 at 16:02
Andrea
7,761144954
asked Jan 2 at 4:01
VTISCHUKVTISCHUK
174
edited Jan 4 at 16:02
Andrea
7,761144954
asked Jan 2 at 4:01
VTISCHUKVTISCHUK
174
edited Jan 4 at 16:02
Andrea
7,761144954
edited Jan 4 at 16:02
Andrea
7,761144954
edited Jan 4 at 16:02
Andrea
7,761144954
7,761144954
asked Jan 2 at 4:01
VTISCHUKVTISCHUK
174
asked Jan 2 at 4:01
VTISCHUKVTISCHUK
174
asked Jan 2 at 4:01
VTISCHUKVTISCHUK
174
174
2
Please show your code attempt.
– Dale Burrell
Jan 2 at 4:05
add a comment |
2
Please show your code attempt.
– Dale Burrell
Jan 2 at 4:05
2
2
Please show your code attempt.
– Dale Burrell
Jan 2 at 4:05
Please show your code attempt.
– Dale Burrell
Jan 2 at 4:05
add a comment |
2 Answers
2
active
oldest
votes
No, there is no variation of PIVOT/UNPIVOT that will let you create that first row in your "converted" output, with the id in one column and the values of type for each of the other columns.
That's going to require some custom select to create the first row, and then UNION with an UNPIVOT to create the rest of the rows.
Since no aggregation is being performed, I don't know why you want to do this in SQL. Personally I would do this in the presentation layer.
answered Jan 2 at 14:54
Tab AllemanTab Alleman
27.1k62442
add a comment |
You can achieve the "converted" layout with dynamic sql, but be aware of performance and security problems of this approach.
Moreover as said by Tab Alleman in his answer this is clearly a work for the presentation layer.
If you still want to do this in SQL Server, here is a possible solution:
CREATE TABLE [dbo].[Test] (
[id] int
,[type] varchar(50)
,NBR varchar(50)
,c1_pos int
,c1_neg int
,c2_pos int
,c2_neg int )
INSERT INTO [dbo].[Test]
values
(8375, 'Type #1', 'P #1', 1, 0, 0, 0)
,(8375, 'Type #2', 'P #2', 0, 1, 0, 0)
,(8375, 'Type #3', 'P #1', 0, 1, 0, 0)
,(8375, 'Type #4', 'P #1', 1, 0, 0, 0)
,(8375, 'Type #5', 'P #1', 0, 0, 0, 0)
--this variable holds the value that will become the header of the first column
declare @first_column_header nvarchar(10)
--this variable holds all the dates that will become column names
declare @headers nvarchar(max)=''
--this variable contains the TSQL dinamically generated
declare @sql nvarchar(max)=''
SELECT @first_column_header = max(id) FROM [dbo].[Test] group by [id]
select @headers = @headers + ', ' + QUOTENAME([type]) from [dbo].[Test]
set @headers = RIGHT(@headers, len(@headers) - 2)
set @sql = @sql + 'select piv.col as ' + QUOTENAME(@first_column_header) + ', ' + @headers + ' '
set @sql = @sql + 'from '
set @sql = @sql + '( '
set @sql = @sql + ' select [type], col, val, ord '
set @sql = @sql + ' from [dbo].[Test] '
set @sql = @sql + ' CROSS APPLY ('
set @sql = @sql + ' VALUES (''NBR'' , cast(NBR as varchar(10)) , 1), '
set @sql = @sql + ' (''c1_pos'' , cast(c1_pos as varchar(10)) , 2), '
set @sql = @sql + ' (''c1_neg'' , cast(c1_neg as varchar(10)) , 3), '
set @sql = @sql + ' (''c2_pos'' , cast(c2_pos as varchar(10)) , 4), '
set @sql = @sql + ' (''c2_neg'' , cast(c2_neg as varchar(10)) , 5) '
set @sql = @sql + ' )CS (col, val, ord) '
set @sql = @sql + ') src '
set @sql = @sql + 'pivot ( max(val) for [type] in (' + @headers + ') ) piv '
set @sql = @sql + 'order by ord'
exec(@sql)
Result:
answered Jan 4 at 15:58
AndreaAndrea
7,761144954
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54001053%2fpivot-unpivot-more-than-one-column%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
No, there is no variation of PIVOT/UNPIVOT that will let you create that first row in your "converted" output, with the id in one column and the values of type for each of the other columns.
That's going to require some custom select to create the first row, and then UNION with an UNPIVOT to create the rest of the rows.
Since no aggregation is being performed, I don't know why you want to do this in SQL. Personally I would do this in the presentation layer.
answered Jan 2 at 14:54
Tab AllemanTab Alleman
27.1k62442
add a comment |
No, there is no variation of PIVOT/UNPIVOT that will let you create that first row in your "converted" output, with the id in one column and the values of type for each of the other columns.
That's going to require some custom select to create the first row, and then UNION with an UNPIVOT to create the rest of the rows.
Since no aggregation is being performed, I don't know why you want to do this in SQL. Personally I would do this in the presentation layer.
answered Jan 2 at 14:54
Tab AllemanTab Alleman
27.1k62442
add a comment |
No, there is no variation of PIVOT/UNPIVOT that will let you create that first row in your "converted" output, with the id in one column and the values of type for each of the other columns.
That's going to require some custom select to create the first row, and then UNION with an UNPIVOT to create the rest of the rows.
Since no aggregation is being performed, I don't know why you want to do this in SQL. Personally I would do this in the presentation layer.
answered Jan 2 at 14:54
Tab AllemanTab Alleman
27.1k62442
No, there is no variation of PIVOT/UNPIVOT that will let you create that first row in your "converted" output, with the id in one column and the values of type for each of the other columns.
That's going to require some custom select to create the first row, and then UNION with an UNPIVOT to create the rest of the rows.
Since no aggregation is being performed, I don't know why you want to do this in SQL. Personally I would do this in the presentation layer.
answered Jan 2 at 14:54
Tab AllemanTab Alleman
27.1k62442
answered Jan 2 at 14:54
Tab AllemanTab Alleman
27.1k62442
answered Jan 2 at 14:54
Tab AllemanTab Alleman
27.1k62442
answered Jan 2 at 14:54
Tab AllemanTab Alleman
27.1k62442
27.1k62442
add a comment |
add a comment |
You can achieve the "converted" layout with dynamic sql, but be aware of performance and security problems of this approach.
Moreover as said by Tab Alleman in his answer this is clearly a work for the presentation layer.
If you still want to do this in SQL Server, here is a possible solution:
CREATE TABLE [dbo].[Test] (
[id] int
,[type] varchar(50)
,NBR varchar(50)
,c1_pos int
,c1_neg int
,c2_pos int
,c2_neg int )
INSERT INTO [dbo].[Test]
values
(8375, 'Type #1', 'P #1', 1, 0, 0, 0)
,(8375, 'Type #2', 'P #2', 0, 1, 0, 0)
,(8375, 'Type #3', 'P #1', 0, 1, 0, 0)
,(8375, 'Type #4', 'P #1', 1, 0, 0, 0)
,(8375, 'Type #5', 'P #1', 0, 0, 0, 0)
--this variable holds the value that will become the header of the first column
declare @first_column_header nvarchar(10)
--this variable holds all the dates that will become column names
declare @headers nvarchar(max)=''
--this variable contains the TSQL dinamically generated
declare @sql nvarchar(max)=''
SELECT @first_column_header = max(id) FROM [dbo].[Test] group by [id]
select @headers = @headers + ', ' + QUOTENAME([type]) from [dbo].[Test]
set @headers = RIGHT(@headers, len(@headers) - 2)
set @sql = @sql + 'select piv.col as ' + QUOTENAME(@first_column_header) + ', ' + @headers + ' '
set @sql = @sql + 'from '
set @sql = @sql + '( '
set @sql = @sql + ' select [type], col, val, ord '
set @sql = @sql + ' from [dbo].[Test] '
set @sql = @sql + ' CROSS APPLY ('
set @sql = @sql + ' VALUES (''NBR'' , cast(NBR as varchar(10)) , 1), '
set @sql = @sql + ' (''c1_pos'' , cast(c1_pos as varchar(10)) , 2), '
set @sql = @sql + ' (''c1_neg'' , cast(c1_neg as varchar(10)) , 3), '
set @sql = @sql + ' (''c2_pos'' , cast(c2_pos as varchar(10)) , 4), '
set @sql = @sql + ' (''c2_neg'' , cast(c2_neg as varchar(10)) , 5) '
set @sql = @sql + ' )CS (col, val, ord) '
set @sql = @sql + ') src '
set @sql = @sql + 'pivot ( max(val) for [type] in (' + @headers + ') ) piv '
set @sql = @sql + 'order by ord'
exec(@sql)
Result:
answered Jan 4 at 15:58
AndreaAndrea
7,761144954
add a comment |
You can achieve the "converted" layout with dynamic sql, but be aware of performance and security problems of this approach.
Moreover as said by Tab Alleman in his answer this is clearly a work for the presentation layer.
If you still want to do this in SQL Server, here is a possible solution:
CREATE TABLE [dbo].[Test] (
[id] int
,[type] varchar(50)
,NBR varchar(50)
,c1_pos int
,c1_neg int
,c2_pos int
,c2_neg int )
INSERT INTO [dbo].[Test]
values
(8375, 'Type #1', 'P #1', 1, 0, 0, 0)
,(8375, 'Type #2', 'P #2', 0, 1, 0, 0)
,(8375, 'Type #3', 'P #1', 0, 1, 0, 0)
,(8375, 'Type #4', 'P #1', 1, 0, 0, 0)
,(8375, 'Type #5', 'P #1', 0, 0, 0, 0)
--this variable holds the value that will become the header of the first column
declare @first_column_header nvarchar(10)
--this variable holds all the dates that will become column names
declare @headers nvarchar(max)=''
--this variable contains the TSQL dinamically generated
declare @sql nvarchar(max)=''
SELECT @first_column_header = max(id) FROM [dbo].[Test] group by [id]
select @headers = @headers + ', ' + QUOTENAME([type]) from [dbo].[Test]
set @headers = RIGHT(@headers, len(@headers) - 2)
set @sql = @sql + 'select piv.col as ' + QUOTENAME(@first_column_header) + ', ' + @headers + ' '
set @sql = @sql + 'from '
set @sql = @sql + '( '
set @sql = @sql + ' select [type], col, val, ord '
set @sql = @sql + ' from [dbo].[Test] '
set @sql = @sql + ' CROSS APPLY ('
set @sql = @sql + ' VALUES (''NBR'' , cast(NBR as varchar(10)) , 1), '
set @sql = @sql + ' (''c1_pos'' , cast(c1_pos as varchar(10)) , 2), '
set @sql = @sql + ' (''c1_neg'' , cast(c1_neg as varchar(10)) , 3), '
set @sql = @sql + ' (''c2_pos'' , cast(c2_pos as varchar(10)) , 4), '
set @sql = @sql + ' (''c2_neg'' , cast(c2_neg as varchar(10)) , 5) '
set @sql = @sql + ' )CS (col, val, ord) '
set @sql = @sql + ') src '
set @sql = @sql + 'pivot ( max(val) for [type] in (' + @headers + ') ) piv '
set @sql = @sql + 'order by ord'
exec(@sql)
Result:
answered Jan 4 at 15:58
AndreaAndrea
7,761144954
add a comment |
You can achieve the "converted" layout with dynamic sql, but be aware of performance and security problems of this approach.
Moreover as said by Tab Alleman in his answer this is clearly a work for the presentation layer.
If you still want to do this in SQL Server, here is a possible solution:
CREATE TABLE [dbo].[Test] (
[id] int
,[type] varchar(50)
,NBR varchar(50)
,c1_pos int
,c1_neg int
,c2_pos int
,c2_neg int )
INSERT INTO [dbo].[Test]
values
(8375, 'Type #1', 'P #1', 1, 0, 0, 0)
,(8375, 'Type #2', 'P #2', 0, 1, 0, 0)
,(8375, 'Type #3', 'P #1', 0, 1, 0, 0)
,(8375, 'Type #4', 'P #1', 1, 0, 0, 0)
,(8375, 'Type #5', 'P #1', 0, 0, 0, 0)
--this variable holds the value that will become the header of the first column
declare @first_column_header nvarchar(10)
--this variable holds all the dates that will become column names
declare @headers nvarchar(max)=''
--this variable contains the TSQL dinamically generated
declare @sql nvarchar(max)=''
SELECT @first_column_header = max(id) FROM [dbo].[Test] group by [id]
select @headers = @headers + ', ' + QUOTENAME([type]) from [dbo].[Test]
set @headers = RIGHT(@headers, len(@headers) - 2)
set @sql = @sql + 'select piv.col as ' + QUOTENAME(@first_column_header) + ', ' + @headers + ' '
set @sql = @sql + 'from '
set @sql = @sql + '( '
set @sql = @sql + ' select [type], col, val, ord '
set @sql = @sql + ' from [dbo].[Test] '
set @sql = @sql + ' CROSS APPLY ('
set @sql = @sql + ' VALUES (''NBR'' , cast(NBR as varchar(10)) , 1), '
set @sql = @sql + ' (''c1_pos'' , cast(c1_pos as varchar(10)) , 2), '
set @sql = @sql + ' (''c1_neg'' , cast(c1_neg as varchar(10)) , 3), '
set @sql = @sql + ' (''c2_pos'' , cast(c2_pos as varchar(10)) , 4), '
set @sql = @sql + ' (''c2_neg'' , cast(c2_neg as varchar(10)) , 5) '
set @sql = @sql + ' )CS (col, val, ord) '
set @sql = @sql + ') src '
set @sql = @sql + 'pivot ( max(val) for [type] in (' + @headers + ') ) piv '
set @sql = @sql + 'order by ord'
exec(@sql)
Result:
answered Jan 4 at 15:58
AndreaAndrea
7,761144954
You can achieve the "converted" layout with dynamic sql, but be aware of performance and security problems of this approach.
Moreover as said by Tab Alleman in his answer this is clearly a work for the presentation layer.
If you still want to do this in SQL Server, here is a possible solution:
CREATE TABLE [dbo].[Test] (
[id] int
,[type] varchar(50)
,NBR varchar(50)
,c1_pos int
,c1_neg int
,c2_pos int
,c2_neg int )
INSERT INTO [dbo].[Test]
values
(8375, 'Type #1', 'P #1', 1, 0, 0, 0)
,(8375, 'Type #2', 'P #2', 0, 1, 0, 0)
,(8375, 'Type #3', 'P #1', 0, 1, 0, 0)
,(8375, 'Type #4', 'P #1', 1, 0, 0, 0)
,(8375, 'Type #5', 'P #1', 0, 0, 0, 0)
--this variable holds the value that will become the header of the first column
declare @first_column_header nvarchar(10)
--this variable holds all the dates that will become column names
declare @headers nvarchar(max)=''
--this variable contains the TSQL dinamically generated
declare @sql nvarchar(max)=''
SELECT @first_column_header = max(id) FROM [dbo].[Test] group by [id]
select @headers = @headers + ', ' + QUOTENAME([type]) from [dbo].[Test]
set @headers = RIGHT(@headers, len(@headers) - 2)
set @sql = @sql + 'select piv.col as ' + QUOTENAME(@first_column_header) + ', ' + @headers + ' '
set @sql = @sql + 'from '
set @sql = @sql + '( '
set @sql = @sql + ' select [type], col, val, ord '
set @sql = @sql + ' from [dbo].[Test] '
set @sql = @sql + ' CROSS APPLY ('
set @sql = @sql + ' VALUES (''NBR'' , cast(NBR as varchar(10)) , 1), '
set @sql = @sql + ' (''c1_pos'' , cast(c1_pos as varchar(10)) , 2), '
set @sql = @sql + ' (''c1_neg'' , cast(c1_neg as varchar(10)) , 3), '
set @sql = @sql + ' (''c2_pos'' , cast(c2_pos as varchar(10)) , 4), '
set @sql = @sql + ' (''c2_neg'' , cast(c2_neg as varchar(10)) , 5) '
set @sql = @sql + ' )CS (col, val, ord) '
set @sql = @sql + ') src '
set @sql = @sql + 'pivot ( max(val) for [type] in (' + @headers + ') ) piv '
set @sql = @sql + 'order by ord'
exec(@sql)
Result:
answered Jan 4 at 15:58
AndreaAndrea
7,761144954
answered Jan 4 at 15:58
AndreaAndrea
7,761144954
answered Jan 4 at 15:58
AndreaAndrea
7,761144954
answered Jan 4 at 15:58
AndreaAndrea
7,761144954
7,761144954
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54001053%2fpivot-unpivot-more-than-one-column%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
Please show your code attempt.
– Dale Burrell
Jan 2 at 4:05