[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Transpose SQL Table??

Posted on 2012-09-03
2
Medium Priority
?
580 Views
Last Modified: 2012-09-03
I have a table in SQL 2008 with:

Record   Fieldname        Fieldlabel  FieldValue
1                  1                 Name        Jeroen
1                  2                 Email        Info@shareit.nu
1                  3                 Active       False
2                  1                 Name       Piet
2                  2                 Email        Info@Champagne.nl
2                  3                 Active       False

I want to get an output like:

Record     Name             Email                             Active
1              Jeroen            Info@ShareIT.nu            False
2              Piet                Info@Champagne.nl       False

Any suggestions how i can do this using SQL Commands?

Thanks

Jeroen
0
Comment
Question by:ShareIT
2 Comments
 
LVL 8

Accepted Solution

by:
Andrei Fomitchev earned 1500 total points
ID: 38361870
Working Example:

DECLARE @t TABLE (Record Int, Fieldname Int, FieldLabel Varchar(200), FieldValue Varchar(200))
INSERT INTO @t VALUES
(1, 1, 'Name', 'Jeroen'),
(1, 2, 'Email', 'Info@shareit.nu'),
(1, 3, 'Active', 'False'),
(2, 1, 'Name', 'Piet'),
(2, 2, 'Email', 'Info@Champagne.nl'),
(2, 3, 'Active', 'False')

SELECT * FROM @t

SELECT Record, MAX(Name) AS Name, MAX(EMail) AS EMail, MAX(Active) AS Active 
FROM (
	SELECT  Record,
	CASE WHEN FieldLabel = 'Name' THEN FieldValue ELSE '' END AS Name,
	CASE WHEN FieldLabel = 'Email' THEN FieldValue ELSE '' END AS Email,
	CASE WHEN FieldLabel = 'Active' THEN FieldValue ELSE '' END AS Active
	FROM @t
) v
GROUP BY Record

Open in new window

0
 

Author Comment

by:ShareIT
ID: 38361969
Thanks, I also found a similar solution using the Select Pivot syntax on:

http://stackoverflow.com/questions/1677645/using-pivot-in-sql-server-2008 

Jeroen
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question