x

Query to condense rows

Hi Guys, is it possible to produce this recordset (bottom block of code) via a query from this table data(higher block of code)? Many thanks, David

index Site Wk1 Wk2 Wk3 Wk4 1 MERCHANT SQUARE LONDON Emp1 NULL NULL NULL 2 MERCHANT SQUARE LONDON Emp2 NULL NULL NULL 3 MERCHANT SQUARE LONDON Emp3 NULL NULL NULL 4 MERCHANT SQUARE LONDON Emp4 NULL NULL NULL 5 COLTON SQUARE LEICESTER Emp5 NULL NULL NULL 6 COLTON SQUARE LEICESTER Emp6 NULL NULL NULL 7 COLTON SQUARE LEICESTER Emp7 NULL NULL NULL 8 MELTON ROAD Emp8 NULL NULL Emp14 9 MELTON ROAD Emp9 NULL NULL Emp15 10 MERCHANT SQUARE LONDON NULL Emp10 NULL NULL 11 MERCHANT SQUARE LONDON NULL Emp11 NULL NULL 12 MERCHANT SQUARE LONDON NULL NULL Emp12 NULL 13 MERCHANT SQUARE LONDON NULL NULL Emp13 NULL

index Site Wk1 Wk2 Wk3 Wk4
MERCHANT SQUARE LONDON Emp1 Emp10 Emp12
MERCHANT SQUARE LONDON Emp2 Emp11 Emp13
MERCHANT SQUARE LONDON Emp3
MERCHANT SQUARE LONDON Emp4
COLTON SQUARE LEICESTER Emp5
COLTON SQUARE LEICESTER Emp6
COLTON SQUARE LEICESTER Emp7
MELTON ROAD Emp8 Emp14
MELTON ROAD Emp9 Emp15

more ▼

asked Dec 21, 2009 at 07:45 PM in Default

avatar image

David 1 1
11 1 1 3

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Yes.

Start by considering a column which indicates the Wk#, and a column to indicate the Employee. Then you can number your rows in the way you want them in the results and pivot it back again.

Like:

WITH 
Normalised AS (
SELECT *,
  CASE 
  WHEN Wk1 IS NOT NULL THEN 'Wk1'
  WHEN Wk2 IS NOT NULL THEN 'Wk2'
  WHEN Wk3 IS NOT NULL THEN 'Wk3'
  WHEN Wk4 IS NOT NULL THEN 'Wk4'
  END AS Wk,
  COALESCE(Wk1,Wk2,Wk3,Wk4) AS Employee
FROM yourTable
)
, Numbered AS
(
SELECT ROW_NUMBER(PARTITION BY Site, Wk ORDER BY index) AS RowNum
   , Site, Wk, Employee
FROM Normalised
)
SELECT *
FROM Numbered n
PIVOT 
(MAX(Employee) FOR Wk in ([Wk1],[Wk2],[Wk3],[Wk4])) p
;
more ▼

answered Dec 22, 2009 at 01:59 AM

avatar image

Rob Farley
5.8k 16 22 28

(comments are locked)
10|1200 characters needed characters left

Hi Rob, I was going to do something similar to you and go for normalized...then I thought, ah, I'll go with something convoluted :) ...of course more joins will be needed to accommodate more combinations of special rows like Melton road. I think David has some of the same data modelers as my clients

CREATE TABLE #ubernonsense([index] int,[site] varchar(50),wk1 varchar(10),wk2 varchar(10),wk3 varchar(10),wk4 varchar(10)) INSERT #ubernonsense SELECT 1,'MERCHANT SQUARE LONDON','Emp1',NULL,NULL,NULL UNION ALL SELECT 2,'MERCHANT SQUARE LONDON','Emp2',NULL,NULL,NULL UNION ALL SELECT 3,'MERCHANT SQUARE LONDON','Emp3',NULL,NULL,NULL UNION ALL SELECT 4,'MERCHANT SQUARE LONDON','Emp4',NULL,NULL,NULL UNION ALL SELECT 5,'COLTON SQUARE LEICESTER','Emp5',NULL,NULL,NULL UNION ALL SELECT 6,'COLTON SQUARE LEICESTER','Emp6',NULL,NULL,NULL UNION ALL SELECT 7,'COLTON SQUARE LEICESTER','Emp7',NULL,NULL,NULL UNION ALL SELECT 8,'MELTON ROAD','Emp8',NULL,NULL,'Emp14' UNION ALL SELECT 9,'MELTON ROAD','Emp9',NULL,NULL,'Emp15' UNION ALL SELECT 10,'MERCHANT SQUARE LONDON',NULL,'Emp10',NULL,NULL UNION ALL SELECT 11,'MERCHANT SQUARE LONDON',NULL,'Emp11',NULL,NULL UNION ALL SELECT 12,'MERCHANT SQUARE LONDON',NULL,NULL,'Emp12',NULL UNION ALL SELECT 13,'MERCHANT SQUARE LONDON',NULL,NULL,'Emp13',NULL

SELECT * FROM #ubernonsense

;WITH cte1 AS( SELECT ,[r]=ROW_NUMBER() OVER (PARTITION BY [site], CASE WHEN wk1 IS NULL THEN 0 ELSE 1 END, CASE WHEN wk2 IS NULL THEN 0 ELSE 1 END, CASE WHEN wk3 IS NULL THEN 0 ELSE 1 END, CASE WHEN wk4 IS NULL THEN 0 ELSE 1 END ORDER BY [site]) FROM #ubernonsense WHERE wk1 IS NOT NULL ) ,cte2 AS( SELECT ,[r]=ROW_NUMBER() OVER (PARTITION BY [site], CASE WHEN wk1 IS NULL THEN 0 ELSE 1 END, CASE WHEN wk2 IS NULL THEN 0 ELSE 1 END, CASE WHEN wk3 IS NULL THEN 0 ELSE 1 END, CASE WHEN wk4 IS NULL THEN 0 ELSE 1 END ORDER BY [site]) FROM #ubernonsense WHERE wk2 IS NOT NULL ) ,cte3 AS( SELECT ,[r]=ROW_NUMBER() OVER (PARTITION BY [site], CASE WHEN wk1 IS NULL THEN 0 ELSE 1 END, CASE WHEN wk2 IS NULL THEN 0 ELSE 1 END, CASE WHEN wk3 IS NULL THEN 0 ELSE 1 END, CASE WHEN wk4 IS NULL THEN 0 ELSE 1 END ORDER BY site) FROM #ubernonsense WHERE wk3 IS NOT NULL ) ,cte4 AS( SELECT ,[r]=ROW_NUMBER() OVER (PARTITION BY site, CASE WHEN wk1 IS NULL THEN 0 ELSE 1 END, CASE WHEN wk2 IS NULL THEN 0 ELSE 1 END, CASE WHEN wk3 IS NULL THEN 0 ELSE 1 END, CASE WHEN wk4 IS NULL THEN 0 ELSE 1 END ORDER BY site) FROM #ubernonsense WHERE wk4 IS NOT NULL ) SELECT
COALESCE(cte1.[site],cte2.[site],cte3.[site],cte4.[site])[Site] ,cte1.wk1,cte2.wk2,cte3.wk3,cte4.wk4 FROM cte1 LEFT JOIN cte2 on cte1.r=cte2.r AND cte1.[site]=cte2.[site] LEFT JOIN cte3 on cte2.r=cte3.r AND cte2.[site]=cte3.[site] LEFT JOIN cte4 on cte1.r=cte4.r AND cte1.[site]=cte4.[site]

DROP TABLE #ubernonsense

more ▼

answered Dec 22, 2009 at 02:51 AM

avatar image

Scot Hauder 5
2 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2029
x1089

asked: Dec 21, 2009 at 07:45 PM

Seen: 2195 times

Last Updated: Dec 22, 2009 at 07:22 AM

Copyright 2017 Redgate Software. Privacy Policy