Ggtcf

Example of a Mathematician/Physicist whose Other Publications during their PhD eclipsed their PhD Thesis

Is there an analogue of projective spaces for proper schemes?

Why do we use the plural of movies in this phrase "We went to the movies last night."?

Make solar eclipses exceedingly rare, but still have new moons

Interfacing a button to MCU (and PC) with 50m long cable

Would this house-rule that treats advantage as a +1 to the roll instead (and disadvantage as -1) and allows them to stack be balanced?

Why do professional authors make "consistency" mistakes? And how to avoid them?

What happens if you roll doubles 3 times then land on "Go to jail?"

Complex fractions

Received an invoice from my ex-employer billing me for training; how to handle?

Inappropriate reference requests from Journal reviewers

Help understanding this unsettling image of Titan, Epimetheus, and Saturn's rings?

What happened in Rome, when the western empire "fell"?

Limits on contract work without pre-agreed price/contract (UK)

What does convergence in distribution "in the Gromov–Hausdorff" sense mean?

Can we say or write : "No, it'sn't"?

Return the Closest Prime Number

Why do airplanes bank sharply to the right after air-to-air refueling?

Are there any limitations on attacking while grappling?

If Nick Fury and Coulson already knew about aliens (Kree and Skrull) why did they wait until Thor's appearance to start making weapons?

Contours of a clandestine nature

What benefits would be gained by using human laborers instead of drones in deep sea mining?

Elegant way to replace substring in a regex with optional groups in Python?

Non-deterministic sum of floats

Pervasive pivot data

0

I have a table with in there following data

ID|LabelID|Value
1 |1 |3
1 |2 |1
1 |3 |15
2 |1 |5
2 |2 |7
2 |3 |5

I want to get the following as result within a pervasive database

ID|Label1|Label2|Label3
1 |3 |1 |15
2 |5 |7 |5

Anyone an idea? I did try some things and the best result I can get is the following:

ID|Label1|Label2|Label3
1 |3 | |
1 | |1 |
1 | | |15
2 |5 | |
2 | |7 |
2 | | |5

sql pervasive pervasive-sql

share|improve this question

asked Mar 8 at 14:15

TimVKTimVK

1,066616

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What does your query look like? What version of PSQL?
  
  – mirtheil
  Mar 8 at 14:51
  

  
  
  
  

add a comment | 

0

I have a table with in there following data

ID|LabelID|Value
1 |1 |3
1 |2 |1
1 |3 |15
2 |1 |5
2 |2 |7
2 |3 |5

I want to get the following as result within a pervasive database

ID|Label1|Label2|Label3
1 |3 |1 |15
2 |5 |7 |5

Anyone an idea? I did try some things and the best result I can get is the following:

ID|Label1|Label2|Label3
1 |3 | |
1 | |1 |
1 | | |15
2 |5 | |
2 | |7 |
2 | | |5

sql pervasive pervasive-sql

share|improve this question

asked Mar 8 at 14:15

TimVKTimVK

1,066616

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What does your query look like? What version of PSQL?
  
  – mirtheil
  Mar 8 at 14:51
  

  
  
  
  

add a comment | 

I have a table with in there following data

ID|LabelID|Value
1 |1 |3
1 |2 |1
1 |3 |15
2 |1 |5
2 |2 |7
2 |3 |5

I want to get the following as result within a pervasive database

ID|Label1|Label2|Label3
1 |3 |1 |15
2 |5 |7 |5

Anyone an idea? I did try some things and the best result I can get is the following:

ID|Label1|Label2|Label3
1 |3 | |
1 | |1 |
1 | | |15
2 |5 | |
2 | |7 |
2 | | |5

sql pervasive pervasive-sql

share|improve this question

asked Mar 8 at 14:15

TimVKTimVK

1,066616

ID|LabelID|Value
1 |1 |3
1 |2 |1
1 |3 |15
2 |1 |5
2 |2 |7
2 |3 |5

ID|Label1|Label2|Label3
1 |3 |1 |15
2 |5 |7 |5

ID|Label1|Label2|Label3
1 |3 | |
1 | |1 |
1 | | |15
2 |5 | |
2 | |7 |
2 | | |5

ID|LabelID|Value
1 |1 |3
1 |2 |1
1 |3 |15
2 |1 |5
2 |2 |7
2 |3 |5

ID|LabelID|Value
1 |1 |3
1 |2 |1
1 |3 |15
2 |1 |5
2 |2 |7
2 |3 |5

ID|Label1|Label2|Label3
1 |3 |1 |15
2 |5 |7 |5

ID|Label1|Label2|Label3
1 |3 |1 |15
2 |5 |7 |5

ID|Label1|Label2|Label3
1 |3 | |
1 | |1 |
1 | | |15
2 |5 | |
2 | |7 |
2 | | |5

ID|Label1|Label2|Label3
1 |3 | |
1 | |1 |
1 | | |15
2 |5 | |
2 | |7 |
2 | | |5

share|improve this question

asked Mar 8 at 14:15

TimVKTimVK

1,066616

share|improve this question

asked Mar 8 at 14:15

TimVKTimVK

1,066616

asked Mar 8 at 14:15

TimVKTimVK

1,066616

asked Mar 8 at 14:15

TimVKTimVK

1,066616

2 Answers
2

active

oldest

votes

1

Have fun... :-)

Should work in most versions of SQL. There are vendor specific PIVOT statements which are also an option.

This was the way to do it prior to PIVOT clause.

drop table #test;
create table #test (ID int, LabelID int, Value int);

insert into #test values (1, 1, 3)
,(1, 2, 1)
,(1, 3, 15)
,(2, 1, 5)
,(2, 2, 7)
,(2, 3, 5);

select ID
 ,sum(case when LabelID = 1 then Value else null end) as Label1
 ,sum(case when LabelID = 2 then Value else null end) as Label2
 ,sum(case when LabelID = 3 then Value else null end) as Label3
 from #test
group by ID;

share|improve this answer

answered Mar 8 at 15:29

Liam CaffreyLiam Caffrey

379

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Here is how to do it using PIVOT... select ID ,[1] as Label1 ,[2] as Label2 ,[3] as Label3 from #test pivot (sum (Value) for LabelID in ([1],[2],[3])) as XYX
  
  – Liam Caffrey
  Mar 8 at 15:35
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Pervasive PSQL does not support PIVOT.
  
  – mirtheil
  Mar 8 at 16:48
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I hadn't heard of Pervasive SQL until now. Does the example/style that I posted work in PSQL; I guess it should? mirtheil, your query also works in SQL Server. It requires 3 nested loops according to the SQL Server execution plan versus none in the example I posted.
  
  – Liam Caffrey
  Mar 8 at 17:07
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Your query does work on my PSQL install.
  
  – mirtheil
  Mar 8 at 18:07
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Cool 😎, always refreshing to see how portable sql is.
  
  – Liam Caffrey
  Mar 8 at 18:34
  

  
  
  
  

 | 
show 1 more comment

1

Here's a query that worked for me.

create table psqlPivot (id integer, labelid integer, val integer);
insert into psqlPivot values (1, 1,3);
insert into psqlPivot values (1, 2,1);
insert into psqlPivot values (1, 3,15);
insert into psqlPivot values (2, 1,5);
insert into psqlPivot values (2, 2,7);
insert into psqlPivot values (2, 3,5);

select distinct v.id, (select a.val as label1 from psqlPivot a where a.labelid = 1 and a.id = v.id)
,(select b.val as label2 from psqlPivot b where b.labelid = 2 and b.id = v.id)
,(select c.val as label3 from psqlPivot c where c.labelid = 3 and c.id = v.id)
from psqlpivot v

share|improve this answer

answered Mar 8 at 16:49

mirtheilmirtheil

7,09012124

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Tried this and worked but took quite a while to load the data (4 selects takes too much time)
  
  – TimVK
  Mar 11 at 14:38
  

  
  
  
  

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
);



);

draft saved

draft discarded

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

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55065025%2fpervasive-pivot-data%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

1

Have fun... :-)

Should work in most versions of SQL. There are vendor specific PIVOT statements which are also an option.

This was the way to do it prior to PIVOT clause.

drop table #test;
create table #test (ID int, LabelID int, Value int);

insert into #test values (1, 1, 3)
,(1, 2, 1)
,(1, 3, 15)
,(2, 1, 5)
,(2, 2, 7)
,(2, 3, 5);

select ID
 ,sum(case when LabelID = 1 then Value else null end) as Label1
 ,sum(case when LabelID = 2 then Value else null end) as Label2
 ,sum(case when LabelID = 3 then Value else null end) as Label3
 from #test
group by ID;

share|improve this answer

answered Mar 8 at 15:29

Liam CaffreyLiam Caffrey

379

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Here is how to do it using PIVOT... select ID ,[1] as Label1 ,[2] as Label2 ,[3] as Label3 from #test pivot (sum (Value) for LabelID in ([1],[2],[3])) as XYX
  
  – Liam Caffrey
  Mar 8 at 15:35
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Pervasive PSQL does not support PIVOT.
  
  – mirtheil
  Mar 8 at 16:48
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I hadn't heard of Pervasive SQL until now. Does the example/style that I posted work in PSQL; I guess it should? mirtheil, your query also works in SQL Server. It requires 3 nested loops according to the SQL Server execution plan versus none in the example I posted.
  
  – Liam Caffrey
  Mar 8 at 17:07
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Your query does work on my PSQL install.
  
  – mirtheil
  Mar 8 at 18:07
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Cool 😎, always refreshing to see how portable sql is.
  
  – Liam Caffrey
  Mar 8 at 18:34
  

  
  
  
  

 | 
show 1 more comment

1

Have fun... :-)

Should work in most versions of SQL. There are vendor specific PIVOT statements which are also an option.

This was the way to do it prior to PIVOT clause.

drop table #test;
create table #test (ID int, LabelID int, Value int);

insert into #test values (1, 1, 3)
,(1, 2, 1)
,(1, 3, 15)
,(2, 1, 5)
,(2, 2, 7)
,(2, 3, 5);

select ID
 ,sum(case when LabelID = 1 then Value else null end) as Label1
 ,sum(case when LabelID = 2 then Value else null end) as Label2
 ,sum(case when LabelID = 3 then Value else null end) as Label3
 from #test
group by ID;

share|improve this answer

answered Mar 8 at 15:29

Liam CaffreyLiam Caffrey

379

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Here is how to do it using PIVOT... select ID ,[1] as Label1 ,[2] as Label2 ,[3] as Label3 from #test pivot (sum (Value) for LabelID in ([1],[2],[3])) as XYX
  
  – Liam Caffrey
  Mar 8 at 15:35
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Pervasive PSQL does not support PIVOT.
  
  – mirtheil
  Mar 8 at 16:48
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I hadn't heard of Pervasive SQL until now. Does the example/style that I posted work in PSQL; I guess it should? mirtheil, your query also works in SQL Server. It requires 3 nested loops according to the SQL Server execution plan versus none in the example I posted.
  
  – Liam Caffrey
  Mar 8 at 17:07
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Your query does work on my PSQL install.
  
  – mirtheil
  Mar 8 at 18:07
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Cool 😎, always refreshing to see how portable sql is.
  
  – Liam Caffrey
  Mar 8 at 18:34
  

  
  
  
  

 | 
show 1 more comment

Have fun... :-)

Should work in most versions of SQL. There are vendor specific PIVOT statements which are also an option.

This was the way to do it prior to PIVOT clause.

drop table #test;
create table #test (ID int, LabelID int, Value int);

insert into #test values (1, 1, 3)
,(1, 2, 1)
,(1, 3, 15)
,(2, 1, 5)
,(2, 2, 7)
,(2, 3, 5);

select ID
 ,sum(case when LabelID = 1 then Value else null end) as Label1
 ,sum(case when LabelID = 2 then Value else null end) as Label2
 ,sum(case when LabelID = 3 then Value else null end) as Label3
 from #test
group by ID;

share|improve this answer

answered Mar 8 at 15:29

Liam CaffreyLiam Caffrey

379

drop table #test;
create table #test (ID int, LabelID int, Value int);

insert into #test values (1, 1, 3)
,(1, 2, 1)
,(1, 3, 15)
,(2, 1, 5)
,(2, 2, 7)
,(2, 3, 5);

select ID
 ,sum(case when LabelID = 1 then Value else null end) as Label1
 ,sum(case when LabelID = 2 then Value else null end) as Label2
 ,sum(case when LabelID = 3 then Value else null end) as Label3
 from #test
group by ID;

share|improve this answer

answered Mar 8 at 15:29

Liam CaffreyLiam Caffrey

379

answered Mar 8 at 15:29

Liam CaffreyLiam Caffrey

379

answered Mar 8 at 15:29

Liam CaffreyLiam Caffrey

379

1

Here's a query that worked for me.

create table psqlPivot (id integer, labelid integer, val integer);
insert into psqlPivot values (1, 1,3);
insert into psqlPivot values (1, 2,1);
insert into psqlPivot values (1, 3,15);
insert into psqlPivot values (2, 1,5);
insert into psqlPivot values (2, 2,7);
insert into psqlPivot values (2, 3,5);

select distinct v.id, (select a.val as label1 from psqlPivot a where a.labelid = 1 and a.id = v.id)
,(select b.val as label2 from psqlPivot b where b.labelid = 2 and b.id = v.id)
,(select c.val as label3 from psqlPivot c where c.labelid = 3 and c.id = v.id)
from psqlpivot v

share|improve this answer

answered Mar 8 at 16:49

mirtheilmirtheil

7,09012124

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Tried this and worked but took quite a while to load the data (4 selects takes too much time)
  
  – TimVK
  Mar 11 at 14:38
  

  
  
  
  

add a comment | 

1

Here's a query that worked for me.

create table psqlPivot (id integer, labelid integer, val integer);
insert into psqlPivot values (1, 1,3);
insert into psqlPivot values (1, 2,1);
insert into psqlPivot values (1, 3,15);
insert into psqlPivot values (2, 1,5);
insert into psqlPivot values (2, 2,7);
insert into psqlPivot values (2, 3,5);

select distinct v.id, (select a.val as label1 from psqlPivot a where a.labelid = 1 and a.id = v.id)
,(select b.val as label2 from psqlPivot b where b.labelid = 2 and b.id = v.id)
,(select c.val as label3 from psqlPivot c where c.labelid = 3 and c.id = v.id)
from psqlpivot v

share|improve this answer

answered Mar 8 at 16:49

mirtheilmirtheil

7,09012124

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Tried this and worked but took quite a while to load the data (4 selects takes too much time)
  
  – TimVK
  Mar 11 at 14:38
  

  
  
  
  

add a comment | 

Here's a query that worked for me.

create table psqlPivot (id integer, labelid integer, val integer);
insert into psqlPivot values (1, 1,3);
insert into psqlPivot values (1, 2,1);
insert into psqlPivot values (1, 3,15);
insert into psqlPivot values (2, 1,5);
insert into psqlPivot values (2, 2,7);
insert into psqlPivot values (2, 3,5);

select distinct v.id, (select a.val as label1 from psqlPivot a where a.labelid = 1 and a.id = v.id)
,(select b.val as label2 from psqlPivot b where b.labelid = 2 and b.id = v.id)
,(select c.val as label3 from psqlPivot c where c.labelid = 3 and c.id = v.id)
from psqlpivot v

share|improve this answer

answered Mar 8 at 16:49

mirtheilmirtheil

7,09012124

create table psqlPivot (id integer, labelid integer, val integer);
insert into psqlPivot values (1, 1,3);
insert into psqlPivot values (1, 2,1);
insert into psqlPivot values (1, 3,15);
insert into psqlPivot values (2, 1,5);
insert into psqlPivot values (2, 2,7);
insert into psqlPivot values (2, 3,5);

select distinct v.id, (select a.val as label1 from psqlPivot a where a.labelid = 1 and a.id = v.id)
,(select b.val as label2 from psqlPivot b where b.labelid = 2 and b.id = v.id)
,(select c.val as label3 from psqlPivot c where c.labelid = 3 and c.id = v.id)
from psqlpivot v

share|improve this answer

answered Mar 8 at 16:49

mirtheilmirtheil

7,09012124

answered Mar 8 at 16:49

mirtheilmirtheil

7,09012124

answered Mar 8 at 16:49

mirtheilmirtheil

7,09012124