Converting Access Pivot Table to SQL Server

Question

I'm having trouble converting a MS Access pivot table over to SQL Server. Was hoping someone might help..

TRANSFORM First(contacts.value) AS FirstOfvalue SELECT contacts.contactid FROM contacts RIGHT JOIN contactrecord ON contacts.[detailid] = contactrecord.[detailid] GROUP BY contacts.contactid PIVOT contactrecord.wellknownname ;

Edit: Responding to some of the comments

Contacts table has three fields

contactid | detailid | value | 1 1 Scott

contactrecord has something like

detailid | wellknownname 1 | FirstName 2 | Address1 3 | foobar

contractrecord is dyanamic in that the user at anytime can create a field to be added to contacts

the access query pulls out

contactid | FirstName | Address1 | foobar 1 | Scott | null | null

which is the pivot on the wellknownname. The key here is that the number of columns is dynamic since the user can, at anytime, create another field for the contact. Being new to pivot tables altogether, I'm wondering how I can recreate this access query in sql server.

As for transform... that's a built in access function. More information is found about it here. First() will just take the first result on that matching row.

I hope this helps and appreciate all the help.

If you replace transform to some standard command it will be easier to help. It would also help if we saw your data and your expected output — t-clausen.dk
– t-clausen.dk, Commented Jul 9, 2011 at 22:48
@t-clausen.dk: From what I've learned here on SO, it seems like TRANSFORM is the standard command (in MS Access) for pivoting, and removing it, or rather replacing it with a corresponding T-SQL construct, is what this question about. But I agree with you about the examples, they would help immensely. — Andriy M
– Andriy M, Commented Jul 9, 2011 at 23:19
@Scott: Not all people, possibly not many of them either, know MS Access and T-SQL well enough to help you. @t-clausen.dk is right: posting sample data and output would really help those of us who know T-SQL sufficiently well to suggest a good solution. — Andriy M
– Andriy M, Commented Jul 9, 2011 at 23:19

Scott · Accepted Answer · 2014-05-23 14:09:07Z

I quick search for dynamic pivot tables comes up with this article.

After renaming things in his last query on the page I came up with this:

DECLARE @PivotColumnHeaders VARCHAR(max); SELECT @PivotColumnHeaders = COALESCE(@PivotColumnHeaders + ',['+ CAST(wellknownname as varchar) + ']','['+ CAST(wellknownname as varchar) + ']') FROM contactrecord; DECLARE @PivotTableSQL NVARCHAR(max); SET @PivotTableSQL = N' SELECT * FROM ( SELECT c.contactid, cr.wellknownname, c.value FROM contacts c RIGHT JOIN contactrecord cr on c.detailid = cr.detailid ) as pivotData pivot( min(value) for wellknownname in (' + @PivotColumnHeaders +') ) as pivotTable ' ; execute(@PivotTableSQL);

which despite its ugliness, it does the job

@t-clausen.dk I'm not to worried about sorting from the sql side of things. Everything gets pushed out to some .NET code. However, next challenge is getting this into a view.

Collectives™ on Stack Overflow

Converting Access Pivot Table to SQL Server

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related