SQL Server error using the CONCAT function in a pivot aggregated function
1
I have a SQL query which outputs a pivot.
What I am trying to do is add a CONCAT function to the pivot but I am getting the following error:
'CONCAT' is not a recognized aggregate function.
My current code (Which gives the error):
SELECT * FROM (
SELECT dateadd(minute,(datediff(minute,0,Time)/15)*15,0) Time, AREA, COUNT(BLOB) BLOBs, CARS
FROM
(SELECT BLOB, Time, CARS, DRIVER, [MK], AREA, Tier FROM (
SELECT [BLOB Nbr] BLOB, Time, CARS, [From PT], [To PT], [From PN], [To PN], DRIVER, [MK],
CASE AREA
WHEN '01' THEN '02' WHEN '03' THEN '02'
WHEN '05' THEN '06' WHEN '07' THEN '06'
WHEN '09' THEN '10' WHEN '11' THEN '10'
ELSE AREA END AREA,
Position, Tier
FROM (
SELECT [BLOB Nbr], T_IME Time, [C NAME] CARS, [From PT], [To PT], [From PN], [To PN], [C V] DRIVER, [MK],
CASE WHEN [From PT] = 'DRIVER' THEN LEFT(RIGHT([From PN],6),2) ELSE LEFT(RIGHT([To PN],6),2) END AREA,
CASE WHEN [From PT] = 'DRIVER' THEN LEFT(RIGHT([From PN],4),2) ELSE LEFT(RIGHT([To PN],4),2) END Position,
CASE WHEN [From PT] = 'DRIVER' THEN RIGHT([From PN],2) ELSE RIGHT([To PN],2) END Tier
FROM mytable c
WHERE ([From PT] = 'DRIVER' OR [To PT] = 'DRIVER')
) a ) b ) c
GROUP BY dateadd(minute,(datediff(minute,0,Time)/15)*15,0), AREA, CARS) src pivot(CONCAT(sum(BLOBs),CARs) for AREA in ([02],[06],[10])) piv;
If I change my second line to (Removing CARs):
SELECT dateadd(minute,(datediff(minute,0,Time)/15)*15,0) Time, AREA, COUNT(BLOB) BLOBs
And if I change my GROUP BY to (Removing CARs and the CONCAT function):
GROUP BY dateadd(minute,(datediff(minute,0,Time)/15)*15,0), AREA) src pivot(sum(BLOBs) for AREA in ([02],[06],[10])) piv;
It works but I want the concatenation in the results.
Current working output:
Time 02 06 10
2018-05-07 16:00:00.000 11 NULL NULL
2018-05-07 16:15:00.000 2 7 NULL
2018-05-07 16:30:00.000 NULL 8 NULL
2018-05-07 16:45:00.000 9 NULL NULL
2018-05-07 17:00:00.000 9 NULL 8
Expected output (With CONCAT):
Time 02 06 10
2018-05-07 16:00:00.000 BMW11 NULL NULL
2018-05-07 16:15:00.000 BMW2 KIA7 NULL
2018-05-07 16:30:00.000 NULL KIA8 NULL
2018-05-07 16:45:00.000 BMW9 NULL NULL
2018-05-07 17:00:00.000 BMW9 NULL FIAT8
Sample Data from the query, without the PIVOT:
Time AREA BLOBs CARs
2018-05-07 16:00:00.000 02 11 BMW
2018-05-07 16:15:00.000 02 2 BMW
2018-05-07 16:15:00.000 06 7 KIA
2018-05-07 16:30:00.000 06 8 KIA
2018-05-07 16:45:00.000 02 9 BMW
2018-05-07 17:00:00.000 02 9 BMW
2018-05-07 17:00:00.000 10 8 FIAT
Raw Sample Data from table:
sql
sql-server sql-server-2012 asked Nov 19 '18 at 11:18
MattMatt
10.9k216698
add a comment |
1
I have a SQL query which outputs a pivot.
What I am trying to do is add a CONCAT function to the pivot but I am getting the following error:
'CONCAT' is not a recognized aggregate function.
My current code (Which gives the error):
SELECT * FROM (
SELECT dateadd(minute,(datediff(minute,0,Time)/15)*15,0) Time, AREA, COUNT(BLOB) BLOBs, CARS
FROM
(SELECT BLOB, Time, CARS, DRIVER, [MK], AREA, Tier FROM (
SELECT [BLOB Nbr] BLOB, Time, CARS, [From PT], [To PT], [From PN], [To PN], DRIVER, [MK],
CASE AREA
WHEN '01' THEN '02' WHEN '03' THEN '02'
WHEN '05' THEN '06' WHEN '07' THEN '06'
WHEN '09' THEN '10' WHEN '11' THEN '10'
ELSE AREA END AREA,
Position, Tier
FROM (
SELECT [BLOB Nbr], T_IME Time, [C NAME] CARS, [From PT], [To PT], [From PN], [To PN], [C V] DRIVER, [MK],
CASE WHEN [From PT] = 'DRIVER' THEN LEFT(RIGHT([From PN],6),2) ELSE LEFT(RIGHT([To PN],6),2) END AREA,
CASE WHEN [From PT] = 'DRIVER' THEN LEFT(RIGHT([From PN],4),2) ELSE LEFT(RIGHT([To PN],4),2) END Position,
CASE WHEN [From PT] = 'DRIVER' THEN RIGHT([From PN],2) ELSE RIGHT([To PN],2) END Tier
FROM mytable c
WHERE ([From PT] = 'DRIVER' OR [To PT] = 'DRIVER')
) a ) b ) c
GROUP BY dateadd(minute,(datediff(minute,0,Time)/15)*15,0), AREA, CARS) src pivot(CONCAT(sum(BLOBs),CARs) for AREA in ([02],[06],[10])) piv;
If I change my second line to (Removing CARs):
SELECT dateadd(minute,(datediff(minute,0,Time)/15)*15,0) Time, AREA, COUNT(BLOB) BLOBs
And if I change my GROUP BY to (Removing CARs and the CONCAT function):
GROUP BY dateadd(minute,(datediff(minute,0,Time)/15)*15,0), AREA) src pivot(sum(BLOBs) for AREA in ([02],[06],[10])) piv;
It works but I want the concatenation in the results.
Current working output:
Time 02 06 10
2018-05-07 16:00:00.000 11 NULL NULL
2018-05-07 16:15:00.000 2 7 NULL
2018-05-07 16:30:00.000 NULL 8 NULL
2018-05-07 16:45:00.000 9 NULL NULL
2018-05-07 17:00:00.000 9 NULL 8
Expected output (With CONCAT):
Time 02 06 10
2018-05-07 16:00:00.000 BMW11 NULL NULL
2018-05-07 16:15:00.000 BMW2 KIA7 NULL
2018-05-07 16:30:00.000 NULL KIA8 NULL
2018-05-07 16:45:00.000 BMW9 NULL NULL
2018-05-07 17:00:00.000 BMW9 NULL FIAT8
Sample Data from the query, without the PIVOT:
Time AREA BLOBs CARs
2018-05-07 16:00:00.000 02 11 BMW
2018-05-07 16:15:00.000 02 2 BMW
2018-05-07 16:15:00.000 06 7 KIA
2018-05-07 16:30:00.000 06 8 KIA
2018-05-07 16:45:00.000 02 9 BMW
2018-05-07 17:00:00.000 02 9 BMW
2018-05-07 17:00:00.000 10 8 FIAT
Raw Sample Data from table:
sql
sql-server sql-server-2012 asked Nov 19 '18 at 11:18
MattMatt
10.9k216698
This would likely be far easier to answer with some sample data and expected results.
– Larnu
Nov 19 '18 at 11:19
Adding now :) ...
– Matt
Nov 19 '18 at 11:20
What about the sample data? That's just the expected output and current output.
– Larnu
Nov 19 '18 at 11:56
@Larnu Added output without the pivot
– Matt
Nov 19 '18 at 13:34
add a comment |
1
1
1
I have a SQL query which outputs a pivot.
What I am trying to do is add a CONCAT function to the pivot but I am getting the following error:
'CONCAT' is not a recognized aggregate function.
My current code (Which gives the error):
SELECT * FROM (
SELECT dateadd(minute,(datediff(minute,0,Time)/15)*15,0) Time, AREA, COUNT(BLOB) BLOBs, CARS
FROM
(SELECT BLOB, Time, CARS, DRIVER, [MK], AREA, Tier FROM (
SELECT [BLOB Nbr] BLOB, Time, CARS, [From PT], [To PT], [From PN], [To PN], DRIVER, [MK],
CASE AREA
WHEN '01' THEN '02' WHEN '03' THEN '02'
WHEN '05' THEN '06' WHEN '07' THEN '06'
WHEN '09' THEN '10' WHEN '11' THEN '10'
ELSE AREA END AREA,
Position, Tier
FROM (
SELECT [BLOB Nbr], T_IME Time, [C NAME] CARS, [From PT], [To PT], [From PN], [To PN], [C V] DRIVER, [MK],
CASE WHEN [From PT] = 'DRIVER' THEN LEFT(RIGHT([From PN],6),2) ELSE LEFT(RIGHT([To PN],6),2) END AREA,
CASE WHEN [From PT] = 'DRIVER' THEN LEFT(RIGHT([From PN],4),2) ELSE LEFT(RIGHT([To PN],4),2) END Position,
CASE WHEN [From PT] = 'DRIVER' THEN RIGHT([From PN],2) ELSE RIGHT([To PN],2) END Tier
FROM mytable c
WHERE ([From PT] = 'DRIVER' OR [To PT] = 'DRIVER')
) a ) b ) c
GROUP BY dateadd(minute,(datediff(minute,0,Time)/15)*15,0), AREA, CARS) src pivot(CONCAT(sum(BLOBs),CARs) for AREA in ([02],[06],[10])) piv;
If I change my second line to (Removing CARs):
SELECT dateadd(minute,(datediff(minute,0,Time)/15)*15,0) Time, AREA, COUNT(BLOB) BLOBs
And if I change my GROUP BY to (Removing CARs and the CONCAT function):
GROUP BY dateadd(minute,(datediff(minute,0,Time)/15)*15,0), AREA) src pivot(sum(BLOBs) for AREA in ([02],[06],[10])) piv;
It works but I want the concatenation in the results.
Current working output:
Time 02 06 10
2018-05-07 16:00:00.000 11 NULL NULL
2018-05-07 16:15:00.000 2 7 NULL
2018-05-07 16:30:00.000 NULL 8 NULL
2018-05-07 16:45:00.000 9 NULL NULL
2018-05-07 17:00:00.000 9 NULL 8
Expected output (With CONCAT):
Time 02 06 10
2018-05-07 16:00:00.000 BMW11 NULL NULL
2018-05-07 16:15:00.000 BMW2 KIA7 NULL
2018-05-07 16:30:00.000 NULL KIA8 NULL
2018-05-07 16:45:00.000 BMW9 NULL NULL
2018-05-07 17:00:00.000 BMW9 NULL FIAT8
Sample Data from the query, without the PIVOT:
Time AREA BLOBs CARs
2018-05-07 16:00:00.000 02 11 BMW
2018-05-07 16:15:00.000 02 2 BMW
2018-05-07 16:15:00.000 06 7 KIA
2018-05-07 16:30:00.000 06 8 KIA
2018-05-07 16:45:00.000 02 9 BMW
2018-05-07 17:00:00.000 02 9 BMW
2018-05-07 17:00:00.000 10 8 FIAT
Raw Sample Data from table:
sql
sql-server sql-server-2012 asked Nov 19 '18 at 11:18
MattMatt
10.9k216698
I have a SQL query which outputs a pivot.
What I am trying to do is add a CONCAT function to the pivot but I am getting the following error:
'CONCAT' is not a recognized aggregate function.
My current code (Which gives the error):
SELECT * FROM (
SELECT dateadd(minute,(datediff(minute,0,Time)/15)*15,0) Time, AREA, COUNT(BLOB) BLOBs, CARS
FROM
(SELECT BLOB, Time, CARS, DRIVER, [MK], AREA, Tier FROM (
SELECT [BLOB Nbr] BLOB, Time, CARS, [From PT], [To PT], [From PN], [To PN], DRIVER, [MK],
CASE AREA
WHEN '01' THEN '02' WHEN '03' THEN '02'
WHEN '05' THEN '06' WHEN '07' THEN '06'
WHEN '09' THEN '10' WHEN '11' THEN '10'
ELSE AREA END AREA,
Position, Tier
FROM (
SELECT [BLOB Nbr], T_IME Time, [C NAME] CARS, [From PT], [To PT], [From PN], [To PN], [C V] DRIVER, [MK],
CASE WHEN [From PT] = 'DRIVER' THEN LEFT(RIGHT([From PN],6),2) ELSE LEFT(RIGHT([To PN],6),2) END AREA,
CASE WHEN [From PT] = 'DRIVER' THEN LEFT(RIGHT([From PN],4),2) ELSE LEFT(RIGHT([To PN],4),2) END Position,
CASE WHEN [From PT] = 'DRIVER' THEN RIGHT([From PN],2) ELSE RIGHT([To PN],2) END Tier
FROM mytable c
WHERE ([From PT] = 'DRIVER' OR [To PT] = 'DRIVER')
) a ) b ) c
GROUP BY dateadd(minute,(datediff(minute,0,Time)/15)*15,0), AREA, CARS) src pivot(CONCAT(sum(BLOBs),CARs) for AREA in ([02],[06],[10])) piv;
If I change my second line to (Removing CARs):
SELECT dateadd(minute,(datediff(minute,0,Time)/15)*15,0) Time, AREA, COUNT(BLOB) BLOBs
And if I change my GROUP BY to (Removing CARs and the CONCAT function):
GROUP BY dateadd(minute,(datediff(minute,0,Time)/15)*15,0), AREA) src pivot(sum(BLOBs) for AREA in ([02],[06],[10])) piv;
It works but I want the concatenation in the results.
Current working output:
Time 02 06 10
2018-05-07 16:00:00.000 11 NULL NULL
2018-05-07 16:15:00.000 2 7 NULL
2018-05-07 16:30:00.000 NULL 8 NULL
2018-05-07 16:45:00.000 9 NULL NULL
2018-05-07 17:00:00.000 9 NULL 8
Expected output (With CONCAT):
Time 02 06 10
2018-05-07 16:00:00.000 BMW11 NULL NULL
2018-05-07 16:15:00.000 BMW2 KIA7 NULL
2018-05-07 16:30:00.000 NULL KIA8 NULL
2018-05-07 16:45:00.000 BMW9 NULL NULL
2018-05-07 17:00:00.000 BMW9 NULL FIAT8
Sample Data from the query, without the PIVOT:
Time AREA BLOBs CARs
2018-05-07 16:00:00.000 02 11 BMW
2018-05-07 16:15:00.000 02 2 BMW
2018-05-07 16:15:00.000 06 7 KIA
2018-05-07 16:30:00.000 06 8 KIA
2018-05-07 16:45:00.000 02 9 BMW
2018-05-07 17:00:00.000 02 9 BMW
2018-05-07 17:00:00.000 10 8 FIAT
Raw Sample Data from table:
sql
sql-server sql-server-2012 sql
sql-server sql-server-2012 asked Nov 19 '18 at 11:18
MattMatt
10.9k216698
asked Nov 19 '18 at 11:18
MattMatt
10.9k216698
edited Nov 19 '18 at 12:24
Matt
asked Nov 19 '18 at 11:18
MattMatt
10.9k216698
asked Nov 19 '18 at 11:18
MattMatt
10.9k216698
asked Nov 19 '18 at 11:18
MattMatt
10.9k216698
10.9k216698
This would likely be far easier to answer with some sample data and expected results.
– Larnu
Nov 19 '18 at 11:19
Adding now :) ...
– Matt
Nov 19 '18 at 11:20
What about the sample data? That's just the expected output and current output.
– Larnu
Nov 19 '18 at 11:56
@Larnu Added output without the pivot
– Matt
Nov 19 '18 at 13:34
add a comment |
This would likely be far easier to answer with some sample data and expected results.
– Larnu
Nov 19 '18 at 11:19
Adding now :) ...
– Matt
Nov 19 '18 at 11:20
What about the sample data? That's just the expected output and current output.
– Larnu
Nov 19 '18 at 11:56
@Larnu Added output without the pivot
– Matt
Nov 19 '18 at 13:34
This would likely be far easier to answer with some sample data and expected results.
– Larnu
Nov 19 '18 at 11:19
This would likely be far easier to answer with some sample data and expected results.
– Larnu
Nov 19 '18 at 11:19
Adding now :) ...
– Matt
Nov 19 '18 at 11:20
Adding now :) ...
– Matt
Nov 19 '18 at 11:20
What about the sample data? That's just the expected output and current output.
– Larnu
Nov 19 '18 at 11:56
What about the sample data? That's just the expected output and current output.
– Larnu
Nov 19 '18 at 11:56
@Larnu Added output without the pivot
– Matt
Nov 19 '18 at 13:34
@Larnu Added output without the pivot
– Matt
Nov 19 '18 at 13:34
add a comment |
2 Answers
2
active
oldest
votes
1
Try this,
CREATE TABLE #PIVOT (Time DATETIME, AREA INT, BLOBs INT, CARs VARChar (10))
insert into #PIVOT values
('2018-05-07 16:00:00.000', 02, 11 ,'BMW')
,('2018-05-07 16:15:00.000', 02, 2 ,'BMW')
,('2018-05-07 16:15:00.000', 06, 7 ,'KIA')
,('2018-05-07 16:30:00.000', 06, 8 ,'KIA')
,('2018-05-07 16:45:00.000', 02, 9 ,'BMW')
,('2018-05-07 17:00:00.000', 02, 9 ,'BMW')
,('2018-05-07 17:00:00.000', 10, 8 ,'FIA')
select
Time, CARs + convert (varchar (10), [02]) [02]
, CARs + convert (varchar (10), [06]) [06], CARs + convert (varchar (10), [10]) [10]
from (
select
*
from #PIVOT
) p
pivot
(
max (BLOBs) for area in ([02],[06], [10])
) t
Time 02 06 10
2018-05-07 16:00:00.000 BMW11 NULL NULL
2018-05-07 16:15:00.000 BMW2 NULL NULL
2018-05-07 16:45:00.000 BMW9 NULL NULL
2018-05-07 17:00:00.000 BMW9 NULL NULL
2018-05-07 17:00:00.000 NULL NULL FIA8
2018-05-07 16:15:00.000 NULL KIA7 NULL
2018-05-07 16:30:00.000 NULL KIA8 NULL
Let me work for dynamic query.
answered Nov 19 '18 at 19:50
RanjithRanjith
1438
Brilliant solution!
– Matt
Nov 20 '18 at 11:51
add a comment |
1
You can try below - you need to use an aggregate function in pivot so in your case you can use max(CONCAT(sum(BLOBs),CARs))
SELECT * FROM (
SELECT dateadd(minute,(datediff(minute,0,Time)/15)*15,0) Time, AREA, COUNT(BLOB) BLOBs, CARS
FROM
(SELECT BLOB, Time, CARS, DRIVER, [MK], AREA, Tier FROM (
SELECT [BLOB Nbr] BLOB, Time, CARS, [From PT], [To PT], [From PN], [To PN], DRIVER, [MK],
CASE AREA
WHEN '01' THEN '02' WHEN '03' THEN '02'
WHEN '05' THEN '06' WHEN '07' THEN '06'
WHEN '09' THEN '10' WHEN '11' THEN '10'
ELSE AREA END AREA,
Position, Tier
FROM (
SELECT [BLOB Nbr], T_IME Time, [C NAME] CARS, [From PT], [To PT], [From PN], [To PN], [C V] DRIVER, [MK],
CASE WHEN [From PT] = 'DRIVER' THEN LEFT(RIGHT([From PN],6),2) ELSE LEFT(RIGHT([To PN],6),2) END AREA,
CASE WHEN [From PT] = 'DRIVER' THEN LEFT(RIGHT([From PN],4),2) ELSE LEFT(RIGHT([To PN],4),2) END Position,
CASE WHEN [From PT] = 'DRIVER' THEN RIGHT([From PN],2) ELSE RIGHT([To PN],2) END Tier
FROM mytable c
WHERE ([From PT] = 'DRIVER' OR [To PT] = 'DRIVER')
) a ) b ) c
GROUP BY dateadd(minute,(datediff(minute,0,Time)/15)*15,0), AREA, CARS) src pivot(max(CONCAT(sum(BLOBs),CARs)) for AREA in ([02],[06],[10])) piv;
edited Nov 19 '18 at 12:24
Matt
10.9k216698
answered Nov 19 '18 at 11:24
fa06fa06
12.7k2917
I'm getting aIncorrect syntax near '('.error on the GROUP BY line
– Matt
Nov 19 '18 at 11:28
@Matt, edited my answer - there was a typo error
– fa06
Nov 19 '18 at 11:30
Still getting the same error unfortunately
– Matt
Nov 19 '18 at 11:31
@Matt, it would be great if you create a db fiddle dbfiddle.uk here then it would be easy to find the error for me
– fa06
Nov 19 '18 at 11:38
@fa86 I have added some sample data to the question
– Matt
Nov 19 '18 at 23:32
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53373504%2fsql-server-error-using-the-concat-function-in-a-pivot-aggregated-function%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
1
Try this,
CREATE TABLE #PIVOT (Time DATETIME, AREA INT, BLOBs INT, CARs VARChar (10))
insert into #PIVOT values
('2018-05-07 16:00:00.000', 02, 11 ,'BMW')
,('2018-05-07 16:15:00.000', 02, 2 ,'BMW')
,('2018-05-07 16:15:00.000', 06, 7 ,'KIA')
,('2018-05-07 16:30:00.000', 06, 8 ,'KIA')
,('2018-05-07 16:45:00.000', 02, 9 ,'BMW')
,('2018-05-07 17:00:00.000', 02, 9 ,'BMW')
,('2018-05-07 17:00:00.000', 10, 8 ,'FIA')
select
Time, CARs + convert (varchar (10), [02]) [02]
, CARs + convert (varchar (10), [06]) [06], CARs + convert (varchar (10), [10]) [10]
from (
select
*
from #PIVOT
) p
pivot
(
max (BLOBs) for area in ([02],[06], [10])
) t
Time 02 06 10
2018-05-07 16:00:00.000 BMW11 NULL NULL
2018-05-07 16:15:00.000 BMW2 NULL NULL
2018-05-07 16:45:00.000 BMW9 NULL NULL
2018-05-07 17:00:00.000 BMW9 NULL NULL
2018-05-07 17:00:00.000 NULL NULL FIA8
2018-05-07 16:15:00.000 NULL KIA7 NULL
2018-05-07 16:30:00.000 NULL KIA8 NULL
Let me work for dynamic query.
answered Nov 19 '18 at 19:50
RanjithRanjith
1438
Brilliant solution!
– Matt
Nov 20 '18 at 11:51
add a comment |
1
Try this,
CREATE TABLE #PIVOT (Time DATETIME, AREA INT, BLOBs INT, CARs VARChar (10))
insert into #PIVOT values
('2018-05-07 16:00:00.000', 02, 11 ,'BMW')
,('2018-05-07 16:15:00.000', 02, 2 ,'BMW')
,('2018-05-07 16:15:00.000', 06, 7 ,'KIA')
,('2018-05-07 16:30:00.000', 06, 8 ,'KIA')
,('2018-05-07 16:45:00.000', 02, 9 ,'BMW')
,('2018-05-07 17:00:00.000', 02, 9 ,'BMW')
,('2018-05-07 17:00:00.000', 10, 8 ,'FIA')
select
Time, CARs + convert (varchar (10), [02]) [02]
, CARs + convert (varchar (10), [06]) [06], CARs + convert (varchar (10), [10]) [10]
from (
select
*
from #PIVOT
) p
pivot
(
max (BLOBs) for area in ([02],[06], [10])
) t
Time 02 06 10
2018-05-07 16:00:00.000 BMW11 NULL NULL
2018-05-07 16:15:00.000 BMW2 NULL NULL
2018-05-07 16:45:00.000 BMW9 NULL NULL
2018-05-07 17:00:00.000 BMW9 NULL NULL
2018-05-07 17:00:00.000 NULL NULL FIA8
2018-05-07 16:15:00.000 NULL KIA7 NULL
2018-05-07 16:30:00.000 NULL KIA8 NULL
Let me work for dynamic query.
answered Nov 19 '18 at 19:50
RanjithRanjith
1438
Brilliant solution!
– Matt
Nov 20 '18 at 11:51
add a comment |
1
1
1
Try this,
CREATE TABLE #PIVOT (Time DATETIME, AREA INT, BLOBs INT, CARs VARChar (10))
insert into #PIVOT values
('2018-05-07 16:00:00.000', 02, 11 ,'BMW')
,('2018-05-07 16:15:00.000', 02, 2 ,'BMW')
,('2018-05-07 16:15:00.000', 06, 7 ,'KIA')
,('2018-05-07 16:30:00.000', 06, 8 ,'KIA')
,('2018-05-07 16:45:00.000', 02, 9 ,'BMW')
,('2018-05-07 17:00:00.000', 02, 9 ,'BMW')
,('2018-05-07 17:00:00.000', 10, 8 ,'FIA')
select
Time, CARs + convert (varchar (10), [02]) [02]
, CARs + convert (varchar (10), [06]) [06], CARs + convert (varchar (10), [10]) [10]
from (
select
*
from #PIVOT
) p
pivot
(
max (BLOBs) for area in ([02],[06], [10])
) t
Time 02 06 10
2018-05-07 16:00:00.000 BMW11 NULL NULL
2018-05-07 16:15:00.000 BMW2 NULL NULL
2018-05-07 16:45:00.000 BMW9 NULL NULL
2018-05-07 17:00:00.000 BMW9 NULL NULL
2018-05-07 17:00:00.000 NULL NULL FIA8
2018-05-07 16:15:00.000 NULL KIA7 NULL
2018-05-07 16:30:00.000 NULL KIA8 NULL
Let me work for dynamic query.
answered Nov 19 '18 at 19:50
RanjithRanjith
1438
Try this,
CREATE TABLE #PIVOT (Time DATETIME, AREA INT, BLOBs INT, CARs VARChar (10))
insert into #PIVOT values
('2018-05-07 16:00:00.000', 02, 11 ,'BMW')
,('2018-05-07 16:15:00.000', 02, 2 ,'BMW')
,('2018-05-07 16:15:00.000', 06, 7 ,'KIA')
,('2018-05-07 16:30:00.000', 06, 8 ,'KIA')
,('2018-05-07 16:45:00.000', 02, 9 ,'BMW')
,('2018-05-07 17:00:00.000', 02, 9 ,'BMW')
,('2018-05-07 17:00:00.000', 10, 8 ,'FIA')
select
Time, CARs + convert (varchar (10), [02]) [02]
, CARs + convert (varchar (10), [06]) [06], CARs + convert (varchar (10), [10]) [10]
from (
select
*
from #PIVOT
) p
pivot
(
max (BLOBs) for area in ([02],[06], [10])
) t
Time 02 06 10
2018-05-07 16:00:00.000 BMW11 NULL NULL
2018-05-07 16:15:00.000 BMW2 NULL NULL
2018-05-07 16:45:00.000 BMW9 NULL NULL
2018-05-07 17:00:00.000 BMW9 NULL NULL
2018-05-07 17:00:00.000 NULL NULL FIA8
2018-05-07 16:15:00.000 NULL KIA7 NULL
2018-05-07 16:30:00.000 NULL KIA8 NULL
Let me work for dynamic query.
answered Nov 19 '18 at 19:50
RanjithRanjith
1438
answered Nov 19 '18 at 19:50
RanjithRanjith
1438
answered Nov 19 '18 at 19:50
RanjithRanjith
1438
answered Nov 19 '18 at 19:50
RanjithRanjith
1438
1438
Brilliant solution!
– Matt
Nov 20 '18 at 11:51
add a comment |
Brilliant solution!
– Matt
Nov 20 '18 at 11:51
Brilliant solution!
– Matt
Nov 20 '18 at 11:51
Brilliant solution!
– Matt
Nov 20 '18 at 11:51
add a comment |
1
You can try below - you need to use an aggregate function in pivot so in your case you can use max(CONCAT(sum(BLOBs),CARs))
SELECT * FROM (
SELECT dateadd(minute,(datediff(minute,0,Time)/15)*15,0) Time, AREA, COUNT(BLOB) BLOBs, CARS
FROM
(SELECT BLOB, Time, CARS, DRIVER, [MK], AREA, Tier FROM (
SELECT [BLOB Nbr] BLOB, Time, CARS, [From PT], [To PT], [From PN], [To PN], DRIVER, [MK],
CASE AREA
WHEN '01' THEN '02' WHEN '03' THEN '02'
WHEN '05' THEN '06' WHEN '07' THEN '06'
WHEN '09' THEN '10' WHEN '11' THEN '10'
ELSE AREA END AREA,
Position, Tier
FROM (
SELECT [BLOB Nbr], T_IME Time, [C NAME] CARS, [From PT], [To PT], [From PN], [To PN], [C V] DRIVER, [MK],
CASE WHEN [From PT] = 'DRIVER' THEN LEFT(RIGHT([From PN],6),2) ELSE LEFT(RIGHT([To PN],6),2) END AREA,
CASE WHEN [From PT] = 'DRIVER' THEN LEFT(RIGHT([From PN],4),2) ELSE LEFT(RIGHT([To PN],4),2) END Position,
CASE WHEN [From PT] = 'DRIVER' THEN RIGHT([From PN],2) ELSE RIGHT([To PN],2) END Tier
FROM mytable c
WHERE ([From PT] = 'DRIVER' OR [To PT] = 'DRIVER')
) a ) b ) c
GROUP BY dateadd(minute,(datediff(minute,0,Time)/15)*15,0), AREA, CARS) src pivot(max(CONCAT(sum(BLOBs),CARs)) for AREA in ([02],[06],[10])) piv;
edited Nov 19 '18 at 12:24
Matt
10.9k216698
answered Nov 19 '18 at 11:24
fa06fa06
12.7k2917
I'm getting aIncorrect syntax near '('.error on the GROUP BY line
– Matt
Nov 19 '18 at 11:28
@Matt, edited my answer - there was a typo error
– fa06
Nov 19 '18 at 11:30
Still getting the same error unfortunately
– Matt
Nov 19 '18 at 11:31
@Matt, it would be great if you create a db fiddle dbfiddle.uk here then it would be easy to find the error for me
– fa06
Nov 19 '18 at 11:38
@fa86 I have added some sample data to the question
– Matt
Nov 19 '18 at 23:32
add a comment |
1
You can try below - you need to use an aggregate function in pivot so in your case you can use max(CONCAT(sum(BLOBs),CARs))
SELECT * FROM (
SELECT dateadd(minute,(datediff(minute,0,Time)/15)*15,0) Time, AREA, COUNT(BLOB) BLOBs, CARS
FROM
(SELECT BLOB, Time, CARS, DRIVER, [MK], AREA, Tier FROM (
SELECT [BLOB Nbr] BLOB, Time, CARS, [From PT], [To PT], [From PN], [To PN], DRIVER, [MK],
CASE AREA
WHEN '01' THEN '02' WHEN '03' THEN '02'
WHEN '05' THEN '06' WHEN '07' THEN '06'
WHEN '09' THEN '10' WHEN '11' THEN '10'
ELSE AREA END AREA,
Position, Tier
FROM (
SELECT [BLOB Nbr], T_IME Time, [C NAME] CARS, [From PT], [To PT], [From PN], [To PN], [C V] DRIVER, [MK],
CASE WHEN [From PT] = 'DRIVER' THEN LEFT(RIGHT([From PN],6),2) ELSE LEFT(RIGHT([To PN],6),2) END AREA,
CASE WHEN [From PT] = 'DRIVER' THEN LEFT(RIGHT([From PN],4),2) ELSE LEFT(RIGHT([To PN],4),2) END Position,
CASE WHEN [From PT] = 'DRIVER' THEN RIGHT([From PN],2) ELSE RIGHT([To PN],2) END Tier
FROM mytable c
WHERE ([From PT] = 'DRIVER' OR [To PT] = 'DRIVER')
) a ) b ) c
GROUP BY dateadd(minute,(datediff(minute,0,Time)/15)*15,0), AREA, CARS) src pivot(max(CONCAT(sum(BLOBs),CARs)) for AREA in ([02],[06],[10])) piv;
edited Nov 19 '18 at 12:24
Matt
10.9k216698
answered Nov 19 '18 at 11:24
fa06fa06
12.7k2917
I'm getting aIncorrect syntax near '('.error on the GROUP BY line
– Matt
Nov 19 '18 at 11:28
@Matt, edited my answer - there was a typo error
– fa06
Nov 19 '18 at 11:30
Still getting the same error unfortunately
– Matt
Nov 19 '18 at 11:31
@Matt, it would be great if you create a db fiddle dbfiddle.uk here then it would be easy to find the error for me
– fa06
Nov 19 '18 at 11:38
@fa86 I have added some sample data to the question
– Matt
Nov 19 '18 at 23:32
add a comment |
1
1
1
You can try below - you need to use an aggregate function in pivot so in your case you can use max(CONCAT(sum(BLOBs),CARs))
SELECT * FROM (
SELECT dateadd(minute,(datediff(minute,0,Time)/15)*15,0) Time, AREA, COUNT(BLOB) BLOBs, CARS
FROM
(SELECT BLOB, Time, CARS, DRIVER, [MK], AREA, Tier FROM (
SELECT [BLOB Nbr] BLOB, Time, CARS, [From PT], [To PT], [From PN], [To PN], DRIVER, [MK],
CASE AREA
WHEN '01' THEN '02' WHEN '03' THEN '02'
WHEN '05' THEN '06' WHEN '07' THEN '06'
WHEN '09' THEN '10' WHEN '11' THEN '10'
ELSE AREA END AREA,
Position, Tier
FROM (
SELECT [BLOB Nbr], T_IME Time, [C NAME] CARS, [From PT], [To PT], [From PN], [To PN], [C V] DRIVER, [MK],
CASE WHEN [From PT] = 'DRIVER' THEN LEFT(RIGHT([From PN],6),2) ELSE LEFT(RIGHT([To PN],6),2) END AREA,
CASE WHEN [From PT] = 'DRIVER' THEN LEFT(RIGHT([From PN],4),2) ELSE LEFT(RIGHT([To PN],4),2) END Position,
CASE WHEN [From PT] = 'DRIVER' THEN RIGHT([From PN],2) ELSE RIGHT([To PN],2) END Tier
FROM mytable c
WHERE ([From PT] = 'DRIVER' OR [To PT] = 'DRIVER')
) a ) b ) c
GROUP BY dateadd(minute,(datediff(minute,0,Time)/15)*15,0), AREA, CARS) src pivot(max(CONCAT(sum(BLOBs),CARs)) for AREA in ([02],[06],[10])) piv;
edited Nov 19 '18 at 12:24
Matt
10.9k216698
answered Nov 19 '18 at 11:24
fa06fa06
12.7k2917
You can try below - you need to use an aggregate function in pivot so in your case you can use max(CONCAT(sum(BLOBs),CARs))
SELECT * FROM (
SELECT dateadd(minute,(datediff(minute,0,Time)/15)*15,0) Time, AREA, COUNT(BLOB) BLOBs, CARS
FROM
(SELECT BLOB, Time, CARS, DRIVER, [MK], AREA, Tier FROM (
SELECT [BLOB Nbr] BLOB, Time, CARS, [From PT], [To PT], [From PN], [To PN], DRIVER, [MK],
CASE AREA
WHEN '01' THEN '02' WHEN '03' THEN '02'
WHEN '05' THEN '06' WHEN '07' THEN '06'
WHEN '09' THEN '10' WHEN '11' THEN '10'
ELSE AREA END AREA,
Position, Tier
FROM (
SELECT [BLOB Nbr], T_IME Time, [C NAME] CARS, [From PT], [To PT], [From PN], [To PN], [C V] DRIVER, [MK],
CASE WHEN [From PT] = 'DRIVER' THEN LEFT(RIGHT([From PN],6),2) ELSE LEFT(RIGHT([To PN],6),2) END AREA,
CASE WHEN [From PT] = 'DRIVER' THEN LEFT(RIGHT([From PN],4),2) ELSE LEFT(RIGHT([To PN],4),2) END Position,
CASE WHEN [From PT] = 'DRIVER' THEN RIGHT([From PN],2) ELSE RIGHT([To PN],2) END Tier
FROM mytable c
WHERE ([From PT] = 'DRIVER' OR [To PT] = 'DRIVER')
) a ) b ) c
GROUP BY dateadd(minute,(datediff(minute,0,Time)/15)*15,0), AREA, CARS) src pivot(max(CONCAT(sum(BLOBs),CARs)) for AREA in ([02],[06],[10])) piv;
edited Nov 19 '18 at 12:24
Matt
10.9k216698
answered Nov 19 '18 at 11:24
fa06fa06
12.7k2917
edited Nov 19 '18 at 12:24
Matt
10.9k216698
edited Nov 19 '18 at 12:24
Matt
10.9k216698
edited Nov 19 '18 at 12:24
Matt
10.9k216698
10.9k216698
answered Nov 19 '18 at 11:24
fa06fa06
12.7k2917
answered Nov 19 '18 at 11:24
fa06fa06
12.7k2917
answered Nov 19 '18 at 11:24
fa06fa06
12.7k2917
12.7k2917
I'm getting aIncorrect syntax near '('.error on the GROUP BY line
– Matt
Nov 19 '18 at 11:28
@Matt, edited my answer - there was a typo error
– fa06
Nov 19 '18 at 11:30
Still getting the same error unfortunately
– Matt
Nov 19 '18 at 11:31
@Matt, it would be great if you create a db fiddle dbfiddle.uk here then it would be easy to find the error for me
– fa06
Nov 19 '18 at 11:38
@fa86 I have added some sample data to the question
– Matt
Nov 19 '18 at 23:32
add a comment |
I'm getting aIncorrect syntax near '('.error on the GROUP BY line
– Matt
Nov 19 '18 at 11:28
@Matt, edited my answer - there was a typo error
– fa06
Nov 19 '18 at 11:30
Still getting the same error unfortunately
– Matt
Nov 19 '18 at 11:31
@Matt, it would be great if you create a db fiddle dbfiddle.uk here then it would be easy to find the error for me
– fa06
Nov 19 '18 at 11:38
@fa86 I have added some sample data to the question
– Matt
Nov 19 '18 at 23:32
I'm getting a
Incorrect syntax near '('. error on the GROUP BY line– Matt
Nov 19 '18 at 11:28
I'm getting a
Incorrect syntax near '('. error on the GROUP BY line– Matt
Nov 19 '18 at 11:28
@Matt, edited my answer - there was a typo error
– fa06
Nov 19 '18 at 11:30
@Matt, edited my answer - there was a typo error
– fa06
Nov 19 '18 at 11:30
Still getting the same error unfortunately
– Matt
Nov 19 '18 at 11:31
Still getting the same error unfortunately
– Matt
Nov 19 '18 at 11:31
@Matt, it would be great if you create a db fiddle dbfiddle.uk here then it would be easy to find the error for me
– fa06
Nov 19 '18 at 11:38
@Matt, it would be great if you create a db fiddle dbfiddle.uk here then it would be easy to find the error for me
– fa06
Nov 19 '18 at 11:38
@fa86 I have added some sample data to the question
– Matt
Nov 19 '18 at 23:32
@fa86 I have added some sample data to the question
– Matt
Nov 19 '18 at 23:32
add a comment |
draft saved
draft discarded
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.
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53373504%2fsql-server-error-using-the-concat-function-in-a-pivot-aggregated-function%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
This would likely be far easier to answer with some sample data and expected results.
– Larnu
Nov 19 '18 at 11:19
Adding now :) ...
– Matt
Nov 19 '18 at 11:20
What about the sample data? That's just the expected output and current output.
– Larnu
Nov 19 '18 at 11:56
@Larnu Added output without the pivot
– Matt
Nov 19 '18 at 13:34