StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL Pivot on text columns returning multiple rows
primarykey
Id
8316592
data
AcceptedAnswerId
8317306
AnswerCount
2
ClosedDate
CommentCount
5
CommunityOwnedDate
CreationDate
2011-11-29T19:31:24.220
FavoriteCount
3
LastActivityDate
2011-11-29T20:37:39.573
LastEditDate
2011-11-29T20:00:25.637
LastEditorUserId
932109
OwnerUserId
932109
ParentId
0
PostTypeId
1
Score
1
ViewCount
7458
LastEditorDisplayName
text
Body
I've been through a bunch of examples and other questions on SO, but I'm still having an issue with this pivot. <pre><code>select [Address],[Address 1],[Address 2],[Address2],[Birth Day],[City], [Email],[First Name],[Last Name],[Phone],[State],[Zip] from (SELECT *, row_number() over(PARTITION by formid order by formid) as rnk FROM [Sitecore_WebForms].[dbo].[Field] where FormId='C9681226-5951-403D-80A0-00019218785C' ) as SourceTable pivot ( max(value) for [fieldname] in ([Address],[Address 1],[Address 2],[Address2],[Birth Day],[City],[Email], [First Name],[Last Name],[Phone],[State],[Zip]) ) as pt </code></pre> The result comes back with 9 rows, 1 row for each column. How do I get it to combine these rows into a single row? <pre><code>Address Address 1 Address 2 Address2 Birth Day City Email First Name Last Name Phone State Zip NULL NULL NULL NULL NULL NULL NULL FIRS NULL NULL NULL NULL NULL NULL NULL NULL NULL CITY NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL LAST NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL ZIP ADDR NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL DATE NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL EMAI NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL PHON NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL </code></pre> source rows <pre><code>Id FieldId FormId Value Data FieldName CE160D15-EBCC-45B6-AB67-0CCA4AA081C7 09C0317E-E235-469D-AE7E-6CB9C45916EA C9681226-5951-403D-80A0-00019218785C FIRS First Name 393E6220-8CC8-4B4E-A75B-29E1801A9A65 2ED6CAC8-7210-4BBC-9497-3B0E8A5F7CF7 C9681226-5951-403D-80A0-00019218785C LAST Last Name 64F9A694-406E-4385-932E-2D0D68A17FB7 FA5C1C21-CCFC-4E96-A1D8-57AC7B1AEF2C C9681226-5951-403D-80A0-00019218785C ADDR Address 960A2BB5-0B50-4640-8EC8-170677809E64 CA9184AE-99CD-46C0-A72D-47FBD4F768AB C9681226-5951-403D-80A0-00019218785C CITY City CD954912-B24A-4726-9729-2ACD6DDD00C9 E695546B-2BBA-4AFF-9DAF-1B5666E42C74 C9681226-5951-403D-80A0-00019218785C ZIP Zip FD19C907-F779-4CF8-90B7-4E072CDE36BE DF056320-27BD-4E15-BF73-AC0C4A1CF543 C9681226-5951-403D-80A0-00019218785C DATE Birth Day 497AC034-6686-4023-ADE9-CB825F65DBFE 06807268-1689-4DDF-BDE2-08FBA31E56AE C9681226-5951-403D-80A0-00019218785C PHON Phone 8A438C22-9C8E-4759-998F-542917408E38 D9402E5E-09CE-40E6-86F7-D80D99417208 C9681226-5951-403D-80A0-00019218785C EMAI Email </code></pre>
Tags
<sql><sql-server-2008>
Title
SQL Pivot on text columns returning multiple rows
singulars
PostAcceptedAnswerId
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. USILovePaperTowels
UserOwnerUserId
1. USILovePaperTowels
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
2. PO
 singulars
 PostTypePostTypeId
 PTAnswer
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 POSQL Pivot on text columns returning multiple rows
 UserUserId
 USJorriss
 VoteTypeVoteTypeId
 VTFavorite
CommentsPostId

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.