Group_concat as in MySQL is not available in SQL Server up to SQL Server 2016. vNext will introduce STRING_AGG
however that provides equivalent functionality.
You have some manual options though:
- build a scalar function that takes an employee name/id as parameter and shows concatenated role values and apply this function to each employee
- use xml functions
- use an already built aggregate (similar as first option)
A solution for your current case (just took Brad's answer from SO and customized for your table) would be:
SELECT name, LEFT(roles , LEN(roles )-1) AS roles
FROM employee AS extern
CROSS APPLY
(
SELECT role + ','
FROM employee AS intern
WHERE extern.name = intern.name
FOR XML PATH('')
) pre_trimmed (roles)
GROUP BY name, roles;
These details are gathered from different sources:
Without knowing anything about the source data, perhaps this would do what you want?
USE Test;
GO
CREATE TABLE GENDER
(
ORG INT NOT NULL
, GENDER VARCHAR(1) NOT NULL
);
CREATE TABLE AGE
(
ORG INT NOT NULL
, AGE TINYINT
);
CREATE TABLE STATES
(
ORG INT NOT NULL
, STATENAME VARCHAR(255)
);
INSERT INTO Gender (ORG, GENDER) VALUES (1, 'M');
INSERT INTO Gender (ORG, GENDER) VALUES (1, 'F');
INSERT INTO Gender (ORG, GENDER) VALUES (2, 'M');
INSERT INTO Gender (ORG, GENDER) VALUES (2, 'F');
INSERT INTO Gender (ORG, GENDER) VALUES (3, 'M');
INSERT INTO Gender (ORG, GENDER) VALUES (3, 'F');
INSERT INTO AGE (ORG, AGE) VALUES (1,27);
INSERT INTO AGE (ORG, AGE) VALUES (1,28);
INSERT INTO AGE (ORG, AGE) VALUES (1,29);
INSERT INTO AGE (ORG, AGE) VALUES (1,30);
INSERT INTO AGE (ORG, AGE) VALUES (2,37);
INSERT INTO AGE (ORG, AGE) VALUES (2,38);
INSERT INTO AGE (ORG, AGE) VALUES (2,39);
INSERT INTO AGE (ORG, AGE) VALUES (2,40);
INSERT INTO AGE (ORG, AGE) VALUES (3, 2);
INSERT INTO STATES (ORG, STATENAME) VALUES (1,'FL');
INSERT INTO STATES (ORG, STATENAME) VALUES (1,'GA');
INSERT INTO STATES (ORG, STATENAME) VALUES (1,'MN');
INSERT INTO STATES (ORG, STATENAME) VALUES (1,'NM');
INSERT INTO STATES (ORG, STATENAME) VALUES (2,'FL');
INSERT INTO STATES (ORG, STATENAME) VALUES (2,'MN');
INSERT INTO STATES (ORG, STATENAME) VALUES (2,'NM');
INSERT INTO STATES (ORG, STATENAME) VALUES (3,'FL');
INSERT INTO STATES (ORG, STATENAME) VALUES (3,'GA');
INSERT INTO STATES (ORG, STATENAME) VALUES (3,'NM');
CREATE TABLE FACTS
(
ORG INT NOT NULL
, GENDER VARCHAR(1) NULL
, AGE INT NULL
, STATENAME VARCHAR(255) NULL
);
INSERT INTO FACTS (ORG, GENDER, AGE, STATENAME)
SELECT ORG, GENDER, NULL, NULL
FROM GENDER
GROUP BY ORG, GENDER
UNION ALL
SELECT ORG, NULL, AGE, NULL
FROM AGE
GROUP BY ORG, AGE
UNION ALL
SELECT ORG, NULL, NULL, STATENAME
FROM STATES;
SELECT *
FROM FACTS
ORDER BY ORG;
The results:
This will create a FACTS table that has all the data from several source tables. As @ypercube and @jon-seigel said, this really doesn't make much sense; perhaps we are missing something compelling about your setup.
If this is not what you were expecting, please provide the source tables, and any other pertinent details.
Best Answer
Assuming the column is indexed the following should be reasonably efficient.
With two seeks of 10 rows and then a sort of the (up to) 20 returned.
(i.e. potentially something like the below)
Or another possibility (that reduces the number of rows sorted to max 10)
NB: Execution plan above was for the simple table definition
Technically, the Sort on the bottom branch shouldn't be needed either as that too is ordered by Diff, and it would be possible to merge the two ordered results. But I wasn't able to get that plan.
The query has
ORDER BY Diff ASC, YourCol ASC
and not justORDER BY YourCol ASC
, because that was what ended up working to get rid of the Sort in the top branch of the plan. I needed to add the secondary column in (even though it won't ever change the result asYourCol
will be the same for all values with the same Diff) so it would go through the merge join (concatenation) without adding a Sort.SQL Server seems able to infer that an index on X seeked in ascending order will deliver rows ordered by X + Y and no sort is necessary. But it is not able to infer that travelling the index in descending order will deliver rows in the same order as Y-X (or even just unary minus X). Both branches of the plan use an index to avoid a sort but the
TOP 10
in the bottom branch are then sorted byDiff
(even though they are already in that order) to get them in the desired order for the merge.For other queries/table definitions it may be trickier or not possible to get the merge plan with just a sort of one branch - as it relies on finding an ordering expression that SQL Server:
TOP