> ## Content Index
> Fetch the complete content index at: https://blog.tsd.digital/llms.txt
> Use this file to discover other available public pages before exploring further.

# Download any Umbraco document type’s values as crosstab/Excel table
- URL: https://blog.tsd.digital/download-any-umbraco-document-type-s-values-as-crosstabexcel-table/
- Published: 2010-11-09T21:16:24.000Z
- Updated: 2010-11-09T21:16:24.000Z
- Author: Tim Gaunt
- Tags: SQL, Umbraco, Document Type, Useful Script, Web Development, Useful, #Import 2025-04-01 05:37

![image](https://storage.ghost.io/c/64/5b/645b11c2-b436-444c-a523-58dca4ca7bd8/content/images/tim/images/Download-any-document-types-values-as-cr_ECF7/image.png)

In a follow up to my post yesterday -[How to download Umbraco content properties into a crosstab table](http://blogs.thesitedoctor.co.uk/tim/2010/11/08/Download+Umbraco+Content+Properties+Into+A+Crosstab+Table.aspx?ref=blog.tsd.digital) this is the follow up SQL Script that makes it even easier to download any Umbraco document type into Excel.

This SQL Script is fairly simple, basically what it does is it gets the properties associated with the specified document type and then pivots the values so you end up with a table of data that looks like this:

IdProperty 1Property 2Property 3Property n123StringIntDatexxx

## How to use the script

All you need to do is set the parameter "@ContentTypeId" to the document type you want (as in my [previous post](http://blogs.thesitedoctor.co.uk/tim/2010/11/08/Download+Umbraco+Content+Properties+Into+A+Crosstab+Table.aspx?ref=blog.tsd.digital) you can get this by checking out the link on the document type).

Once you set the id, just run the script and voila there's the data.

If you run the code and get "Command(s) completed successfully" then you've not set the id right so double check and try again.

## The Script

DECLARE @cols NVARCHAR(max), @ContentTypeId int SET @ContentTypeId = 1074 SELECT @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT '\],\[' + CONVERT(varchar, Name + ' (' + CONVERT(varchar, id) + ')', 255) FROM dbo.cmsPropertyType WHERE contentTypeId = @ContentTypeId ORDER BY '\],\[' + CONVERT(varchar, Name + ' (' + CONVERT(varchar, id) + ')', 255) FOR XML PATH('') ), 1, 2, '') + '\]' --SELECT @cols DECLARE @query NVARCHAR(max) SET @query = N'SELECT Id, ' + @cols + ' FROM ( SELECT CONVERT(varchar, t.Name + '' ('' + CONVERT(varchar, t.id) + '')'', 255) As \[PropId\], contentNodeId As \[Id\], ISNULL(dataNvarchar, ISNULL(CONVERT(varchar, dataDate), ISNULL(CONVERT(varchar, dataInt), dataNtext))) As \[Value\] FROM dbo.cmsPropertyType t LEFT JOIN dbo.cmsPropertyData d ON t.id = d.propertytypeid WHERE contentTypeId = ' + CONVERT(varchar, @ContentTypeId) + ' ) p PIVOT ( MAX(Value) FOR PropId IN ( '+ @cols +' ) ) AS pvt ORDER BY Id ASC' --PRINT(@query) EXECUTE(@query)