Vertical data set to horizontal data set for many columnsReconciling dm_os_windows_info windows_sku values,...
Fraction within another fraction
What would be some possible ways of escaping higher gravity planets?
Maybe pigeonhole problem?
Why might frozen potatoes require a hechsher?
How can I differentiate duration vs starting time
How do I avoid the "chosen hero" feeling?
Why does this relation fail symmetry and transitivity properties?
Crack the bank account's password!
How to put text above column in minipage?
Critique vs nitpicking
How long has this character been impersonating a Starfleet Officer?
Was there a pre-determined arrangement for the division of Germany in case it surrendered before any Soviet forces entered its territory?
What's the winning box for the King in a Queen against 7th rank Bishop-pawn endgame?
Equivalent of "illegal" for violating civil law
How can I prevent an oracle who can see into the past from knowing everything that has happened?
Do we still track damage on indestructible creatures?
What could cause an entire planet of humans to become aphasic?
When using Volatility with a memory image, what is the Kernel version?
No option to ask a question in https://developer.salesforce.com discussion forums
What can I do to encourage my players to use their consumables?
Create an animation that plots the following two functions
"Starve to death" Vs. "Starve to the point of death"
How to deal with an underperforming subordinate?
Bug in VectorFieldPlot[] with InterpolatingFunction[]?
Vertical data set to horizontal data set for many columns
Reconciling dm_os_windows_info windows_sku values, gathering same info from pre SQL Server 2008 R2 instancesReporting Table DesignSQL join query to show rows with non-existent rows in one tableSQLite Design: Better to have many tables with few columns or few tables with many columns?Data Warehouse design for reporting against data for many time zonesCombining count queries into one table rowIs normalization advisable on transactional data?Efficiently storing sets of key-value pairs with wildly different keysLooking for advice on how to integrate data from 100+ client DB's into a centralized reporting databaseMost efficient way to aggregate data to new table
I have read about pivot function but I am not sure if If it helps me in this situation. Essentially I have to produce my vertical data set horizontally. For instance the first table shows the vertical data set and ideally i would like to convert that into how the second table looks.
Any help would be appreciated!
reporting
asked Nov 17 '14 at 2:52
user2811633user2811633
163
bumped to the homepage by Community♦ 9 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
I have read about pivot function but I am not sure if If it helps me in this situation. Essentially I have to produce my vertical data set horizontally. For instance the first table shows the vertical data set and ideally i would like to convert that into how the second table looks.
Any help would be appreciated!
reporting
asked Nov 17 '14 at 2:52
user2811633user2811633
163
bumped to the homepage by Community♦ 9 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
I have read about pivot function but I am not sure if If it helps me in this situation. Essentially I have to produce my vertical data set horizontally. For instance the first table shows the vertical data set and ideally i would like to convert that into how the second table looks.
Any help would be appreciated!
reporting
asked Nov 17 '14 at 2:52
user2811633user2811633
163
I have read about pivot function but I am not sure if If it helps me in this situation. Essentially I have to produce my vertical data set horizontally. For instance the first table shows the vertical data set and ideally i would like to convert that into how the second table looks.
Any help would be appreciated!
reporting
reporting
asked Nov 17 '14 at 2:52
user2811633user2811633
163
asked Nov 17 '14 at 2:52
user2811633user2811633
163
asked Nov 17 '14 at 2:52
user2811633user2811633
163
asked Nov 17 '14 at 2:52
user2811633user2811633
163
asked Nov 17 '14 at 2:52
user2811633user2811633
163
163
bumped to the homepage by Community♦ 9 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 9 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
create table Vertical(FID int ,[SID]int ,OAID int,FN varchar(10),[Row] int)
insert into Vertical(FID ,[SID],OAID ,FN ,[Row] ) values(3184226,9304,82,'8090',1)
insert into Vertical(FID ,[SID],OAID ,FN ,[Row] ) values(3200394,4140,81,'0016',2)
insert into Vertical(FID ,[SID],OAID ,FN ,[Row] ) values(3203126,10465,81,'0426',3)
insert into Vertical(FID ,[SID],OAID ,FN ,[Row] ) values(3136790,10822,81,'0029',4)
select
OAID,
case when FID_1 is null then FID_2 else FID_1 end as FID_1,
case when SID_1 is null then SID_2 else SID_1 end as SID_1,
case when FN_1 is null then FN_2 else FN_1 end as FN_1,
case when FID_1 is null then FID_3 else FID_2 end as FID_2,
case when SID_1 is null then SID_3 else SID_2 end as SID_2,
case when FN_1 is null then FN_3 else FN_2 end as FN_2,
case when FID_1 is null then FID_4 else FID_3 end as FID_3,
case when SID_1 is null then SID_4 else SID_3 end as SID_3,
case when FN_1 is null then FN_4 else FN_3 end as FN_3
from (
select OAID,
min([1]) as FID_1,
min(SID_1) as SID_1,
min(FN_1) as FN_1,
min([2]) as FID_2,
min(SID_2) as SID_2,
min(FN_2) as FN_2,
min([3]) as FID_3,
min(SID_3) as SID_3,
min(FN_3) as FN_3,
min([4] )as FID_4 ,
min(SID_4) as SID_4 ,
min(FN_4) as FN_4
from
(
select FID,OAID, [1] as FN_1,[2] as FN_2,[3] as FN_3,[4] as FN_4 ,SID_1,SID_2,SID_3,SID_4,rn_2 from
(
select
FID,
OAID,
FN,
[1] as SID_1,
[2] as SID_2,
[3] as SID_3,
[4] as SID_4
,rn_1
,rn_2
from (select *,ROW_NUMBER() over(order by OAID desc,SID) as rn_1,ROW_NUMBER() over(order by OAID desc,SID) as rn_2 from Vertical )as v
pivot(min([SID]) for [Row] in([1],[2],[3],[4])) as p
)as P
pivot(min([FN]) for rn_1 in([1],[2],[3],[4])) as p
)as P
pivot(min([FID]) for rn_2 in([1],[2],[3],[4])) as p
group by OAID
)as A
answered Nov 17 '14 at 8:42
abianariabianari
15810
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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%2fdba.stackexchange.com%2fquestions%2f82799%2fvertical-data-set-to-horizontal-data-set-for-many-columns%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
create table Vertical(FID int ,[SID]int ,OAID int,FN varchar(10),[Row] int)
insert into Vertical(FID ,[SID],OAID ,FN ,[Row] ) values(3184226,9304,82,'8090',1)
insert into Vertical(FID ,[SID],OAID ,FN ,[Row] ) values(3200394,4140,81,'0016',2)
insert into Vertical(FID ,[SID],OAID ,FN ,[Row] ) values(3203126,10465,81,'0426',3)
insert into Vertical(FID ,[SID],OAID ,FN ,[Row] ) values(3136790,10822,81,'0029',4)
select
OAID,
case when FID_1 is null then FID_2 else FID_1 end as FID_1,
case when SID_1 is null then SID_2 else SID_1 end as SID_1,
case when FN_1 is null then FN_2 else FN_1 end as FN_1,
case when FID_1 is null then FID_3 else FID_2 end as FID_2,
case when SID_1 is null then SID_3 else SID_2 end as SID_2,
case when FN_1 is null then FN_3 else FN_2 end as FN_2,
case when FID_1 is null then FID_4 else FID_3 end as FID_3,
case when SID_1 is null then SID_4 else SID_3 end as SID_3,
case when FN_1 is null then FN_4 else FN_3 end as FN_3
from (
select OAID,
min([1]) as FID_1,
min(SID_1) as SID_1,
min(FN_1) as FN_1,
min([2]) as FID_2,
min(SID_2) as SID_2,
min(FN_2) as FN_2,
min([3]) as FID_3,
min(SID_3) as SID_3,
min(FN_3) as FN_3,
min([4] )as FID_4 ,
min(SID_4) as SID_4 ,
min(FN_4) as FN_4
from
(
select FID,OAID, [1] as FN_1,[2] as FN_2,[3] as FN_3,[4] as FN_4 ,SID_1,SID_2,SID_3,SID_4,rn_2 from
(
select
FID,
OAID,
FN,
[1] as SID_1,
[2] as SID_2,
[3] as SID_3,
[4] as SID_4
,rn_1
,rn_2
from (select *,ROW_NUMBER() over(order by OAID desc,SID) as rn_1,ROW_NUMBER() over(order by OAID desc,SID) as rn_2 from Vertical )as v
pivot(min([SID]) for [Row] in([1],[2],[3],[4])) as p
)as P
pivot(min([FN]) for rn_1 in([1],[2],[3],[4])) as p
)as P
pivot(min([FID]) for rn_2 in([1],[2],[3],[4])) as p
group by OAID
)as A
answered Nov 17 '14 at 8:42
abianariabianari
15810
add a comment |
create table Vertical(FID int ,[SID]int ,OAID int,FN varchar(10),[Row] int)
insert into Vertical(FID ,[SID],OAID ,FN ,[Row] ) values(3184226,9304,82,'8090',1)
insert into Vertical(FID ,[SID],OAID ,FN ,[Row] ) values(3200394,4140,81,'0016',2)
insert into Vertical(FID ,[SID],OAID ,FN ,[Row] ) values(3203126,10465,81,'0426',3)
insert into Vertical(FID ,[SID],OAID ,FN ,[Row] ) values(3136790,10822,81,'0029',4)
select
OAID,
case when FID_1 is null then FID_2 else FID_1 end as FID_1,
case when SID_1 is null then SID_2 else SID_1 end as SID_1,
case when FN_1 is null then FN_2 else FN_1 end as FN_1,
case when FID_1 is null then FID_3 else FID_2 end as FID_2,
case when SID_1 is null then SID_3 else SID_2 end as SID_2,
case when FN_1 is null then FN_3 else FN_2 end as FN_2,
case when FID_1 is null then FID_4 else FID_3 end as FID_3,
case when SID_1 is null then SID_4 else SID_3 end as SID_3,
case when FN_1 is null then FN_4 else FN_3 end as FN_3
from (
select OAID,
min([1]) as FID_1,
min(SID_1) as SID_1,
min(FN_1) as FN_1,
min([2]) as FID_2,
min(SID_2) as SID_2,
min(FN_2) as FN_2,
min([3]) as FID_3,
min(SID_3) as SID_3,
min(FN_3) as FN_3,
min([4] )as FID_4 ,
min(SID_4) as SID_4 ,
min(FN_4) as FN_4
from
(
select FID,OAID, [1] as FN_1,[2] as FN_2,[3] as FN_3,[4] as FN_4 ,SID_1,SID_2,SID_3,SID_4,rn_2 from
(
select
FID,
OAID,
FN,
[1] as SID_1,
[2] as SID_2,
[3] as SID_3,
[4] as SID_4
,rn_1
,rn_2
from (select *,ROW_NUMBER() over(order by OAID desc,SID) as rn_1,ROW_NUMBER() over(order by OAID desc,SID) as rn_2 from Vertical )as v
pivot(min([SID]) for [Row] in([1],[2],[3],[4])) as p
)as P
pivot(min([FN]) for rn_1 in([1],[2],[3],[4])) as p
)as P
pivot(min([FID]) for rn_2 in([1],[2],[3],[4])) as p
group by OAID
)as A
answered Nov 17 '14 at 8:42
abianariabianari
15810
add a comment |
create table Vertical(FID int ,[SID]int ,OAID int,FN varchar(10),[Row] int)
insert into Vertical(FID ,[SID],OAID ,FN ,[Row] ) values(3184226,9304,82,'8090',1)
insert into Vertical(FID ,[SID],OAID ,FN ,[Row] ) values(3200394,4140,81,'0016',2)
insert into Vertical(FID ,[SID],OAID ,FN ,[Row] ) values(3203126,10465,81,'0426',3)
insert into Vertical(FID ,[SID],OAID ,FN ,[Row] ) values(3136790,10822,81,'0029',4)
select
OAID,
case when FID_1 is null then FID_2 else FID_1 end as FID_1,
case when SID_1 is null then SID_2 else SID_1 end as SID_1,
case when FN_1 is null then FN_2 else FN_1 end as FN_1,
case when FID_1 is null then FID_3 else FID_2 end as FID_2,
case when SID_1 is null then SID_3 else SID_2 end as SID_2,
case when FN_1 is null then FN_3 else FN_2 end as FN_2,
case when FID_1 is null then FID_4 else FID_3 end as FID_3,
case when SID_1 is null then SID_4 else SID_3 end as SID_3,
case when FN_1 is null then FN_4 else FN_3 end as FN_3
from (
select OAID,
min([1]) as FID_1,
min(SID_1) as SID_1,
min(FN_1) as FN_1,
min([2]) as FID_2,
min(SID_2) as SID_2,
min(FN_2) as FN_2,
min([3]) as FID_3,
min(SID_3) as SID_3,
min(FN_3) as FN_3,
min([4] )as FID_4 ,
min(SID_4) as SID_4 ,
min(FN_4) as FN_4
from
(
select FID,OAID, [1] as FN_1,[2] as FN_2,[3] as FN_3,[4] as FN_4 ,SID_1,SID_2,SID_3,SID_4,rn_2 from
(
select
FID,
OAID,
FN,
[1] as SID_1,
[2] as SID_2,
[3] as SID_3,
[4] as SID_4
,rn_1
,rn_2
from (select *,ROW_NUMBER() over(order by OAID desc,SID) as rn_1,ROW_NUMBER() over(order by OAID desc,SID) as rn_2 from Vertical )as v
pivot(min([SID]) for [Row] in([1],[2],[3],[4])) as p
)as P
pivot(min([FN]) for rn_1 in([1],[2],[3],[4])) as p
)as P
pivot(min([FID]) for rn_2 in([1],[2],[3],[4])) as p
group by OAID
)as A
answered Nov 17 '14 at 8:42
abianariabianari
15810
create table Vertical(FID int ,[SID]int ,OAID int,FN varchar(10),[Row] int)
insert into Vertical(FID ,[SID],OAID ,FN ,[Row] ) values(3184226,9304,82,'8090',1)
insert into Vertical(FID ,[SID],OAID ,FN ,[Row] ) values(3200394,4140,81,'0016',2)
insert into Vertical(FID ,[SID],OAID ,FN ,[Row] ) values(3203126,10465,81,'0426',3)
insert into Vertical(FID ,[SID],OAID ,FN ,[Row] ) values(3136790,10822,81,'0029',4)
select
OAID,
case when FID_1 is null then FID_2 else FID_1 end as FID_1,
case when SID_1 is null then SID_2 else SID_1 end as SID_1,
case when FN_1 is null then FN_2 else FN_1 end as FN_1,
case when FID_1 is null then FID_3 else FID_2 end as FID_2,
case when SID_1 is null then SID_3 else SID_2 end as SID_2,
case when FN_1 is null then FN_3 else FN_2 end as FN_2,
case when FID_1 is null then FID_4 else FID_3 end as FID_3,
case when SID_1 is null then SID_4 else SID_3 end as SID_3,
case when FN_1 is null then FN_4 else FN_3 end as FN_3
from (
select OAID,
min([1]) as FID_1,
min(SID_1) as SID_1,
min(FN_1) as FN_1,
min([2]) as FID_2,
min(SID_2) as SID_2,
min(FN_2) as FN_2,
min([3]) as FID_3,
min(SID_3) as SID_3,
min(FN_3) as FN_3,
min([4] )as FID_4 ,
min(SID_4) as SID_4 ,
min(FN_4) as FN_4
from
(
select FID,OAID, [1] as FN_1,[2] as FN_2,[3] as FN_3,[4] as FN_4 ,SID_1,SID_2,SID_3,SID_4,rn_2 from
(
select
FID,
OAID,
FN,
[1] as SID_1,
[2] as SID_2,
[3] as SID_3,
[4] as SID_4
,rn_1
,rn_2
from (select *,ROW_NUMBER() over(order by OAID desc,SID) as rn_1,ROW_NUMBER() over(order by OAID desc,SID) as rn_2 from Vertical )as v
pivot(min([SID]) for [Row] in([1],[2],[3],[4])) as p
)as P
pivot(min([FN]) for rn_1 in([1],[2],[3],[4])) as p
)as P
pivot(min([FID]) for rn_2 in([1],[2],[3],[4])) as p
group by OAID
)as A
answered Nov 17 '14 at 8:42
abianariabianari
15810
answered Nov 17 '14 at 8:42
abianariabianari
15810
answered Nov 17 '14 at 8:42
abianariabianari
15810
answered Nov 17 '14 at 8:42
abianariabianari
15810
15810
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- 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%2fdba.stackexchange.com%2fquestions%2f82799%2fvertical-data-set-to-horizontal-data-set-for-many-columns%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
