Sql-server – Display columns as one row

aggregatesql serversql-server-2000

I've got a query running on SQL Server 2000 (and using SQL Server Management Studio 10.x)

It results in three columns with multiple rows.

What I am trying to do, and can't get my head round, is to get the multiple rows into one row for each unique item in column 1 (does that make sense).

The query is:

select last_allocated_to as engineer, count(*) as qty, left(post_code, charindex(' ', post_code) - 1) as postcode
from calls with (nolock)
inner join clients on link_to_client=client_Ref
where last_event_status = 'A'
and call_type not in ('VW')
and dbo.dateonly(scheduled_date_Time) = dbo.dateonly(getdate())
and link_to_so in ('OS1','OS2','OS3')
group by last_allocated_to, left(post_code, charindex(' ', post_code) - 1)

The result is (for example):

engineer   qty    postcode
JWH        1      AB25
JWH        1      AB31
JBR        6      EH51
RSC        1      EH51
CPA        1      FK1
RYM        3      FK10
CARPET     1      FK2
RDU        1      FK2
RSC        2      FK2
SWO        5      FK2
BJON       4      FK3
CPA        4      FK3

I want the result to be:

engineer   result
JWH        1 x AB25, 1 x AB31,
JBR        6 x EH51
RSC        1 x EH51
RYM        3 x FK10, 1 x FK2, 2 x FK1
etc etc

Is this at all possible? Or am I on a hiding to nothing?

Best Answer

I suggest that you pipe the results of your query to a temporary table and work from there. You query is already complex enough and to achieve the results you are after you need to perform multiple acceses to the base data.

When your temp table is ready, you can query it like this:

CREATE TABLE #mytable(
   engineer VARCHAR(6) NOT NULL 
  ,qty      INT  NOT NULL
  ,postcode VARCHAR(4) NOT NULL
);
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('JWH',1,'AB25');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('JWH',1,'AB31');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('JBR',6,'EH51');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('RSC',1,'EH51');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('CPA',1,'FK1');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('RYM',3,'FK10');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('CARPET',1,'FK2');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('RDU',1,'FK2');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('RSC',2,'FK2');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('SWO',5,'FK2');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('BJON',4,'FK3');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('CPA',4,'FK3');

WITH engineers AS (
    SELECT DISTINCT engineer
    FROM #mytable
),
preaggregation as (
    SELECT engineer, postcode, sum(qty) as qty
    FROM #mytable
    GROUP BY engineer, postcode
)
SELECT *, STUFF((
    SELECT ',' + CAST(qty AS varchar(10)) + ' x postcode'
    FROM preaggregation 
    WHERE engineer = engineers.engineer
    FOR XML PATH(''), TYPE
).value('.','varchar(max)'),1,1,SPACE(0))
FROM engineers

Some comments about your code:

  • NOLOCK does not mean "go faster": it means "no consistency". Get rid of it, you'll thank me later.
  • Scalar functions are evil, well known performance killers. Avoid them like the plague. In your case, I suppose that the dbo.dateonly function does what's on the tin, so it converts a datetime to a date. You can acheive the same thing with CAST as date (which is still non-SARGable, but doesn't trigger procedural code for each and every row in the input set).

Hope this helps

EDIT: to make the above code compatible with SQL Server 2000, you need another temprary table and (UGH!) a cursor:

IF OBJECT_ID('tempdb..#mytable') IS NOT NULL DROP TABLE #mytable;
IF OBJECT_ID('tempdb..#results') IS NOT NULL DROP TABLE #results;

CREATE TABLE #mytable(
   engineer VARCHAR(6) NOT NULL 
  ,qty      INT  NOT NULL
  ,postcode VARCHAR(4) NOT NULL
);
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('JWH',1,'AB25');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('JWH',1,'AB31');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('JBR',6,'EH51');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('RSC',1,'EH51');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('CPA',1,'FK1');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('RYM',3,'FK10');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('CARPET',1,'FK2');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('RDU',1,'FK2');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('RSC',2,'FK2');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('SWO',5,'FK2');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('BJON',4,'FK3');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('CPA',4,'FK3');




CREATE TABLE #results(
   engineer VARCHAR(6) NOT NULL 
  ,result varchar(8000) NULL
);

INSERT INTO #results (engineer, result)
SELECT DISTINCT engineer, ''
FROM #mytable;

DECLARE eng CURSOR STATIC LOCAL FORWARD_ONLY READ_ONLY
FOR
SELECT engineer, ',' + CAST(qty AS varchar(10)) + ' x ' + postcode
FROM (
    SELECT engineer, postcode, sum(qty) as qty
    FROM #mytable
    GROUP BY engineer, postcode
) AS preaggregation;

DECLARE @eng varchar(6)
DECLARE @result varchar(8000)

OPEN eng
FETCH NEXT FROM eng INTO @eng, @result

WHILE @@FETCH_STATUS = 0
BEGIN

    UPDATE #results 
    SET result = result + @result
    WHERE engineer = @eng;

    FETCH NEXT FROM eng INTO @eng, @result

END

CLOSE eng
DEALLOCATE eng

UPDATE #results
SET result = STUFF(result, 1, 1, SPACE(0))

SELECT * 
FROM #results
Related Question