StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POCrosstab Queries: using fixed values to create additional columns
primarykey
Id
12498570
data
AcceptedAnswerId
0
AnswerCount
2
ClosedDate
CommentCount
0
CommunityOwnedDate
CreationDate
2012-09-19T16:02:41.017
FavoriteCount
1
LastActivityDate
2017-10-11T06:37:08.257
LastEditDate
2017-10-11T06:37:08.257
LastEditorUserId
3820271
OwnerUserId
362712
ParentId
0
PostTypeId
1
Score
1
ViewCount
4222
LastEditorDisplayName
text
Body
Has anyone read the MSDN page on using the TRANSFORM statement to create crosstab queries? <a href="http://msdn.microsoft.com/en-us/library/office/bb208956(v=office.12).aspx" rel="nofollow noreferrer">http://msdn.microsoft.com/en-us/library/office/bb208956(v=office.12).aspx</a> It includes the following assertion, unsupported by code samples: <BLOCKQUOTE> You can also include fixed values for which no data exists to create additional columns.</BLOCKQUOTE> Yes, I would like to create a pivot table with a fixed ordered set of column headings from a pre-existing list. Here's a simplified SQL query: <PRE> TRANSFORM SUM(tblData.Losses) As TotalLosses SELECT tblData.LossType FROM tblData GROUP BY tblData.Region PIVOT tblData.Year;</PRE> I would like to add region names that are not in the table and I would like the regions to appear in a specific order. Yes, I can create a region listing table and left-join it: but that won't impose an arbitrary order, either - Crosstab queries always sort the columns left-to-right alphabetically. And I might just want to add arbitrary fixed values for which no data exists. Here's MSDN's information: <BLOCKQUOTE>Syntax TRANSFORM aggfunction selectstatement PIVOT pivotfield [IN (value1[, value2[, …]])] The TRANSFORM statement has these parts: <ul> <li>aggfunction:	 An SQL aggregate function that operates on the selected data. </li> <li>selectstatement:	 A SELECT statement. </li> <li>pivotfield:	 The field or expression you want to use to create column headings in the query's result set. </li> <li>value1,value2:	 Fixed values used to create column headings. </BLOCKQUOTE></li> </ul> ...And the rest is just fluff for creating a plain-vanilla pivot table from textbook data. So, my question is: <h2>Has anyone ever actually used fixed values to create column headings?</h2> A sample of your SQL would be useful. <hr> This is a question about the published syntax for Microsoft Access SQL. Thank you for not asking why I want to do this, giving lengthy SQL examples that answer the question 'Is there ANSI SQL that does what a TRANSFORM statement does, by hardcoding everything?' or pointing out that this would be easier in Postgres on a mainframe.
Tags
<ms-access><ms-access-2010>
Title
Crosstab Queries: using fixed values to create additional columns
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. This table or related slice is empty.
UserOwnerUserId
1. USNigel Heffernan
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
VotesPostIdCreationDate
1. This table or related slice is empty.
CommentsPostId
1. This table or related slice is empty.

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.