Pivoting data

Question

Pivoting data

Aquilagb

Grasshopper

Points: 19
More actions
October 19, 2016 at 10:35 am

#331475

Hello,
I have a SQL query which works fine and returns 10 columns and one row of data. What I would like is to pivot this data so that the columns are returned as rows and the row of data is a column.
Is this possible?
I have looked at using the pivot function, but that seems to force me into performing a calculation on the data. I simply want to take the data that is returned and display the columns vertically rather than horizontally.
Thanks in advance.
David

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

Aquilagb (10/19/2016)
Hello,
I have a SQL query which works fine and returns 10 columns and one row of data. What I would like is to pivot this data so that the columns are returned as rows and the row of data is a column.
Is this possible?
I have looked at using the pivot function, but that seems to force me into performing a calculation on the data. I simply want to take the data that is returned and display the columns vertically rather than horizontally.
Thanks in advance.
David

care to share your sql query?

maybe also would be good to provide some sample data set up scripts ...see here https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

Check the following article:

http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Aquilagb Grasshopper Points: 19 More actions · Answer 3

Thank you for the reply...

The query is this:

SELECT TOP 1000 [LTStringId]

,[LanguageCode]

,[LTStringType]

,[ElementName]

,[SubElementName]

,[TimeAdded]

,[LastModified]

,[DisplayStringId]

FROM LocalizedText

WHERE LTStringId = 'F4752595-BFB9-E3BC-6643-FAFBEC7EA6EF'

Only one row of data will ever be returned even when the LTStringId is changed.

Thanks,

David

Aquilagb Grasshopper Points: 19 More actions · Answer 4

Hi Luis,

I checked out that article but it still suggests that I need to manipulate the data in order to pivot it. Is there no way I can have the data as it is returned now, but rather than the columns be next to each other horizontally, they are underneath each other vertically?

Apologies if I am not explaining this very well, this is all new to me!

Thanks.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 5

The article that I posted does exactly what you're asking, put the columns as rows. There's no magic way to do it without coding. Here's an example that might have errors due to data types. If you want something tested, read the articles in my signature.

SELECT LT.[LTStringId], u.ColumnName, u.Value

FROM LocalizedText LT

CROSS APPLY (VALUES

([LanguageCode] , 'LanguageCode' )

,([LTStringType] , 'LTStringType' )

,([ElementName] , 'ElementName' )

,([SubElementName] , 'SubElementName' )

,([TimeAdded] , 'TimeAdded' )

,([LastModified] , 'LastModified' )

,([DisplayStringId], 'DisplayStringId'))u(Value,ColumnName)

WHERE LTStringId = 'F4752595-BFB9-E3BC-6643-FAFBEC7EA6EF'

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

drew.allen SSC Guru Points: 76944 More actions · Answer 6

Aquilagb (10/19/2016)
Thank you for the reply...
The query is this:
SELECT TOP 1000 [LTStringId]
,[LanguageCode]
,[LTStringType]
,[ElementName]
,[SubElementName]
,[TimeAdded]
,[LastModified]
,[DisplayStringId]
FROM LocalizedText
WHERE LTStringId = 'F4752595-BFB9-E3BC-6643-FAFBEC7EA6EF'
Only one row of data will ever be returned even when the LTStringId is changed.

If you can only ever return one row of data, why are you selecting the TOP 1000?

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA