May 6, 2016 at 3:52 am
Hi all,
I have session data with members belonging to sessions. Members can belong to one, some or all of the sessions.
I am trying to get a query like the one given below that returns data for all members on all sessions that can be pivoted to give something like this;
s1 s2 s3
mem 1 x x -
mem 2 x - -
mem 3 x x x
(the dashes being nulls or not on that session)
I cant seem to come close but I also know next to nothing about the new sql 2012 features having recently come from 2000.
Thanks for any advice or help,
Rolf
CREATE TABLE #SESSIONS (
SESSIONID int
)
CREATE TABLE #MEMBERS (
MEMBERID INT,
SESSIONID INT
)
INSERT INTO #SESSIONS
SELECT 1
UNION
SELECT 2
UNION
SELECT 3
INSERT INTO #MEMBERS
SELECT 1,1
UNION
SELECT 1,2
UNION
SELECT 2,1
UNION
SELECT 3,1
UNION
SELECT 3,2
UNION
SELECT 3,3
SELECT S.SESSIONID, M.MEMBERID
FROM #SESSIONS S LEFT OUTER JOIN #MEMBERS M ON S.SESSIONID = M.SESSIONID AND M.MEMBERID = 1
DROP TABLE #SESSIONS
DROP TABLE #MEMBERS
May 6, 2016 at 5:38 am
maybe....
CREATE TABLE #MEMBERS (
MEMBERID INT,
SESSIONID INT
)
INSERT INTO #MEMBERS
SELECT 1,1
UNION
SELECT 1,2
UNION
SELECT 2,1
UNION
SELECT 3,1
UNION
SELECT 3,2
UNION
SELECT 3,3
CREATE TABLE #SESSIONS (
SESSIONID int
)
INSERT INTO #SESSIONS
SELECT 1
UNION
SELECT 2
UNION
SELECT 3
SELECT
m.MEMBERID,
MAX(CASE WHEN s.SESSIONID = 1 and m.MEMBERID > 0 THEN 'x' ELSE '-' END) as S1,
MAX(CASE WHEN s.SESSIONID = 2 and m.MEMBERID > 0 THEN 'x' ELSE '-' END) as S2,
MAX(CASE WHEN s.SESSIONID = 3 and m.MEMBERID > 0 THEN 'x' ELSE '-' END) as S3
FROM #SESSIONS AS s
LEFT OUTER JOIN #MEMBERS AS m ON s.SESSIONID = m.SESSIONID
GROUP BY m.MEMBERID
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 10, 2016 at 2:20 am
Thanks for that, that is the structure of my dynamic pivot....should of just done that but I was trying to get to intermediate point without having to pivot which showed all members cross joined with all sessions including those they were not on something like..
memebr sessionid joined
1 1 1
1 2 null
1 3 1
Thanks again.
Rolf
May 10, 2016 at 3:14 am
Hi again,
Got this all working fine and have just been told that the pivot now has to occur over two columns so the data is like this
CREATE TABLE #SESSIONS (
SESSIONID int,
SESSIONDATE SMALLDATETIME,
SESSIONGROUP VARCHAR(150)
)
CREATE TABLE #MEMBERS (
MEMBERID INT,
SESSIONID INT
)
INSERT INTO #SESSIONS
SELECT 1, '01/01/2016', 'GROUP A'
UNION
SELECT 2, '02/01/2016', 'GROUP A'
UNION
SELECT 3, '03/01/2016', 'GROUP A'
UNION
SELECT 4, '01/01/2016', 'GROUP B'
UNION
SELECT 5, '02/01/2016', 'GROUP B'
UNION
SELECT 6, '03/01/2016', 'GROUP B'
INSERT INTO #MEMBERS
SELECT 1,1
UNION
SELECT 1,2
UNION
SELECT 2,1
UNION
SELECT 3,1
UNION
SELECT 3,2
UNION
SELECT 3,3
UNION
SELECT 1,4
UNION
SELECT 2,4
UNION
SELECT 3,4
UNION
SELECT 3,5
UNION
SELECT 3,6
SELECT S.SESSIONGROUP, S.SESSIONDATE, S.SESSIONID, M.MEMBERID
FROM #SESSIONS S LEFT OUTER JOIN #MEMBERS M ON S.SESSIONID = M.SESSIONID AND M.MEMBERID = 1
DROP TABLE #SESSIONS
DROP TABLE #MEMBERS
and the results need to be pivoted around the sessiondate and the sessiongroup like so;
memberid 01/01/2016 - group a 02/01/2016 - group a ..... 03/01/2016 - group b
1 x - -
2 - x -
I am currently reading up on the unpivot command and looking at some examples of that as it looks like it might be the way forward.
Rolf
May 10, 2016 at 3:37 am
SELECT
m.MEMBERID,
MAX(CASE WHEN s.SESSIONID = 1 and m.MEMBERID > 0 THEN 'x' ELSE '-' END) as [01/01/2016-GROUP A],
MAX(CASE WHEN s.SESSIONID = 2 and m.MEMBERID > 0 THEN 'x' ELSE '-' END) as [02/01/2016-GROUP A],
MAX(CASE WHEN s.SESSIONID = 3 and m.MEMBERID > 0 THEN 'x' ELSE '-' END) as [03/01/2016-GROUP A]
FROM #SESSIONS AS s
LEFT OUTER JOIN #MEMBERS AS m ON s.SESSIONID = m.SESSIONID
GROUP BY m.MEMBERID
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply