SQL Server query: convert rows to columns

Question

I have a table containing columns VisitorName, vitising_date, location Id. I need to display record for current month visitor location wise along with last month visitor

SQL FIDDLE

Table schema:

CREATE TABLE visitors
(
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [name] varchar(50),
    [location_id] [bigint],
    [visited_date] [datetime]
);

name   location_id    visited_date
David      1           2016-01-04
John       1           2016-01-02
Andrea     1           2016-01-05
Ely        2           2016-01-04
Andrea     1           2016-02-02
John       1           2016-02-02
Peter      2           2016-02-02

Query for current month record:

select * 
from visitors 
where 
    DATEPART(month, visited_date) = DATEPART(MONTH, GETDATE()) 
    and DATEPART(year, visited_date) = DATEPART(year, getdate())

But i need data to be display last month record along current month something like this

  Locationid   Name     Date        LastMonthVisitor   LastMonthVisitingDate
   1           Andrea   2016-02-02  Andrea              2016-01-05 
   1             John   2016-02-02  David               2016-01-04 
   1            'NA'    'NA'        John                2016-01-02 

   2            Peter  2016-02-02   Ely                 2016-01-04

The record shows list of visitors of current month group by location id (location id is foreign key)
Now I need list of last month visitor group by Location ID

These result are place next to each other order by date. Their might be change where last month visitor record is 5 for location_id 1 and current month visitor would be 2 So finally it will show 5 record. Their is no relation between Current Month visitor name, LastMonth visitor name.

I can do did part on server side C# and dispaly list of CurrentMonthVisitors aong with LastMonthVisitor group by Location ID. But am trying in SQL.

Why does David not appear in your example result set? What relation do John and David have in the second record, and what happened to Ely? — Tim Biegeleisen, Feb 04 '16 at 08:07
@TimBiegeleisen : David is also der but in last month visitor column. Record are in current month order. — Satinder singh, Feb 04 '16 at 08:11
why don't you do two selects and combine? http://stackoverflow.com/questions/27238170/how-to-combine-two-querys-results-into-one-row — , Feb 04 '16 at 08:12
You can try PIVOT & UNPIVOT: https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx — Helio, Feb 04 '16 at 08:49
I don't see any relation in your data. Seems random to me. Did you mess up the location_id for some rows ? — t-clausen.dk, Feb 04 '16 at 08:58
Thinking it through... Why "John" is on the left in the 2nd row, but next to him is "David", while on the 3rd row "John" is on the right and NULL on the left? What is the selection logic/mechanism behind that? Why not John & John on 2nd row, NULL & David on 3rd? The thing is, you did not add the logic/mechanism behind this selection, so all you can do is above mentioned pivoting or the combine I mentioned already — , Feb 04 '16 at 09:17
so this is all I can offer: SELECT visLEFT.location_id, visLEFT.name AS "name", visLEFT.visited_date AS "vdate", visRIGHT.name AS "LastMonthVisitor", visRIGHT.visited_date AS "LastMonthVisitingDate" FROM visitors AS visLEFT INNER JOIN visitors AS visRIGHT ON (visLEFT.location_id = visRIGHT.location_id) WHERE visLEFT.visited_date >= DateAdd(month, -1, current_timestamp) AND (visRIGHT.visited_date <= DateAdd(month, -1, current_timestamp) AND visRIGHT.visited_date >= DateAdd(month, -2, current_timestamp)) ORDER BY 1, 2, 4 — , Feb 04 '16 at 09:21
@Mark: why John is on left side 2nd row, Well thier is no relation between left, right. The result will show list of visitor where visiting date is current month and current year. — Satinder singh, Feb 04 '16 at 09:35
@Satindersingh pivoting and combining rows then is your only option. as the data are not 'relational', so to speak — , Feb 04 '16 at 09:43

t-clausen.dk · Accepted Answer · 2016-02-04T12:29:27.483

Try this, it doesn't give the exact same result, but it seems to me that your expected result doesn't match the data:

;WITH CTE as
(
  SELECT
   *, 
   -- setting a row number based on month, and location
   row_number() over 
     (partition by datediff(month, 0, visited_date), 
     location_id order by visited_date, name) rn,
    datediff(month, 0, visited_date) month
  FROM visitors
  WHERE
    -- current and last month
    visited_date >= dateadd(month, datediff(month, 0, getdate()) - 1, 0) and
    visited_date < dateadd(month, datediff(month, -1, getdate()), 0)
)
SELECT 
  CTE.location_id Locationid,
  COALESCE(CTE.Name, 'N/A') Name,
  CAST(CTE.visited_date as Date) Date,
  COALESCE(CTE2.name, 'N/A') LastMonthVisitor,
  CAST(CTE2.visited_date as Date) LastMonthVisitingDate
FROM CTE
-- full join to get data when there is not matching data for the other month
FULL JOIN
CTE CTE2
ON CTE.rn = CTE2.rn
and CTE.location_id = CTE2.location_id
and CTE.month = CTE2.month-1
WHERE
  -- making sure CTE is last month or CTE2 is current month
  cte.month =datediff(month, 0, getdate()) - 1 or
  cte2.month=datediff(month, 0, getdate())

Thank you so much, above query really works , i just did little change .i.e set CTE2 as current Month here's the sql fiddle http://sqlfiddle.com/#!3/9f050/9. If you dont mind, can you add little explaination in your answer so it helps me to understand it better or nay refference link, thanks again — Satinder singh, Feb 04 '16 at 11:18

SQL Server query: convert rows to columns

1 Answers1