Transpose Query

redxtb

Registered User.
Local time
Today, 16:54
Joined
Feb 11, 2014
Messages
16
need query to transpose data

Code:
ID	numStore	cntStoreManager	cntAstMgr	cntTeamLead	cntReceiver	cntTeamMemb	sumTotalLoss	numDate
1	100	0	0	1	1	10	12	20141227
1493	100	0	0	0	1	12	13	20150124
2985	100	0	1	0	1	11	13	20150221
4477	100	0	1	0	1	10	12	20150328
5969	100	0	1	1	1	11	14	20150425
7461	100	0	1	1	1	13	16	20150523
8953	100	0	1	1	1	13	16	20150627
10445	100	0	1	1	1	12	15	20150725
11937	100	1	1	1	1	11	15	20150822
13429	100	1	1	2	1	9	14	20150926
14921	100	1	1	2	1	9	14	20151024

Code:
numStore	txtJobRoles	20141227	20150124	20150221	20150328	20150425	20150523	20150627	20150725	20150822	20150926	20151024
100	cntStoreManager	0	0	0	0	0	0	0	0	1	1	1
100	cntAstMgr	0	0	1	1	1	1	1	1	1	1	1
100	cntTeamLead	1	0	0	0	1	1	1	1	1	2	2
100	cntReceiver	1	1	1	1	1	1	1	1	1	1	1
100	cntTeamMemb	10	12	11	10	11	13	13	12	11	9	9
100	sumTotalLoss	12	13	13	12	14	16	16	15	15	14	14
 
you need a union query to combine your data into the form of

store jobrole count date

then a crosstab query to get the view you want

It is likely that the original data that created your current view is already in that form or something very similar since you would not have a table that looks like that - unless the data has been created in excel
 

Users who are viewing this thread

Back
Top Bottom