How can I format an output table in SQL Server?

Question

After running this query

SELECT Title , 
CASE WHEN fieldName = 'Supplier Name' THEN fieldvalue
END AS 'Supplier Name',
CASE WHEN fieldname = 'Legal Agreement Name' THEN fieldValue 
END AS 'Legal Agreement Name'
FROM PCM_MultiItems_Metadata
WHERE PROFILEID =254 
      and fieldname in ('Creator Contact Name','Supplier Name','Legal Agreement Name');

I am getting

I have this table generated in sql server for different properties of different assets

How can I format this table in sql so that I get table such as Title becomes my first column, Supplier Name and Legal Name being my 2nd and 3rd columns respectively

My sql query to generate that table is

SELECT Title, FieldName, Fieldvalue 
from PCM_MultiItems_Metadata 
WHERE PROFILEID =254 
  and fieldname in ('Creator Contact Name','Supplier Name','Legal Agreement Name');

I want an output such as

Title      | Supplier Name              | Legal Agreement Name
231457       Bay VALLEY                   IMAGE AND DIGITAL AGREEMENT
232058       BUCKHEAD MEAT;NEWPORT MEAT   SYSCO MEAT BRAND GUDILINE

Create new table way you want, insert data in it, drop old table — Veljko89
– Veljko89, Commented Jan 30, 2018 at 13:47
@SQL_M that isnt the case here, he wants the data within the column to become the column — WhatsThePoint
– WhatsThePoint, Commented Jan 30, 2018 at 13:49
@Sami im not saying the question here isnt too broad, im just saying its not a dupe — WhatsThePoint
– WhatsThePoint, Commented Jan 30, 2018 at 13:51

Aura · Accepted Answer · 2018-01-30 14:19:46Z

0

Can you try following approach:

SELECT Title , 
CASE WHEN fieldName = 'Supplier Name' THEN fieldvalue
END AS 'Supplier Name',
CASE WHEN fieldname = 'Legal Agreement Name' THEN fieldValue 
END AS 'Legal Agreement Name'
FROM PCM_MultiItems_Metadata
WHERE PROFILEID =254 
      and fieldname in ('Creator Contact Name','Supplier Name','Legal Agreement Name');

edited Jan 30, 2018 at 14:19

answered Jan 30, 2018 at 14:06

Aura

1,3032 gold badges18 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Karan Neginhal Over a year ago

thanks for that . Its giving me 2 rows with same title number.

Aura Over a year ago

Add DISTINCT keyword after select, it should solve the duplication issue.

Karan Neginhal Over a year ago

it still doesnt work .Its still giving me 2 rows with same title . Can you see my screenshot attached above ?

Sean Lange Over a year ago

You are on the right path here but you need aggregation to make this work correctly. See my answer.

Sean Lange · Accepted Answer · 2018-01-30 14:46:35Z

0

Using conditional aggregation you can do something like this.

select Title
    , max(case when FieldName = 'Creator Contact Name' then FieldValue end) as CreatorContactName
    , max(case when FieldName = 'Supplier Name' then FieldValue end) as SupplierName
    , max(case when FieldName = 'Legal Agreement Name' then FieldValue end) as LegalAgreementName
from PCM_MultiItems_Metadata 
WHERE PROFILEID = 254 
  and fieldname in ('Creator Contact Name','Supplier Name','Legal Agreement Name')
group by Title

answered Jan 30, 2018 at 14:46

Sean Lange

33.7k3 gold badges28 silver badges42 bronze badges

2 Comments

Karan Neginhal Over a year ago

Thanks it works well on ssms. I am getting an error while running it on ssrs to generate a report . The error is 'could not create a list of fields for the query.Verify that you can connect to the datasource and that your query syntax is correct '. An item with same key has already been added"

Sean Lange Over a year ago

That error occurs when you have multiple columns in your query with the same name. Each column MUST be unique in a report, but in a query in SSMS it doesn't have to be unique.

squillman · Accepted Answer · 2018-01-30 14:50:26Z

0

Try this:

SELECT DISTINCT
    t1.Title,
    t1.FieldValue as [Supplier Name],
    t2.FieldValue as [Legal Agreement Name]
FROM PCM_MultiItems_Metadata t1
INNER JOIN PCM_MultiItems_Metadata t2 on t2.Title= t1.Title
WHERE t1.FieldName = 'Supplier Name'
AND t2.FieldName = 'Legal Agreement Name'

This will work for the limited snapshot of source data you provided. If there are other columns or values for FieldName you will need to adjust it accordingly.

EDIT
Adjusted to give an example of how to use for more values of FieldName.

SELECT DISTINCT
    t1.Title,
    t1.FieldValue as [Supplier Name],
    t2.FieldValue as [Legal Agreement Name],
    t3.FieldValue as [Field 3],
    t4.FieldValue as [Field 4],
FROM PCM_MultiItems_Metadata t1
INNER JOIN PCM_MultiItems_Metadata t2 on t2.Title= t1.Title
INNER JOIN PCM_MultiItems_Metadata t3 on t3.Title= t1.Title
INNER JOIN PCM_MultiItems_Metadata t4 on t4.Title= t1.Title
WHERE t1.FieldName = 'Supplier Name'
AND t2.FieldName = 'Legal Agreement Name'
AND t3.FieldName = 'Field 3',
AND t4.FieldName = 'Field 4'

This is a static solution based on a static set of values for FieldName.

edited Jan 30, 2018 at 14:50

answered Jan 30, 2018 at 14:38

squillman

13.7k3 gold badges45 silver badges65 bronze badges

5 Comments

Karan Neginhal Over a year ago

Thanks . It works for this 2 field names but I have 15-20 Field Names. How can I do that ? So ultimately we are looking at 15 -20 columns instead of 2 now

squillman Over a year ago

Using this approach you will need to add additional JOINS and extend the WHERE clause.

Karan Neginhal Over a year ago

Thanks it works . Now how do I use it for dynamic set of values . In the example I gave above it is based on a specific profile. In the above example it was profile 254. Now how do I use it for other profiles ?

squillman Over a year ago

This already returns data for all profiles. You just add PROFILEID to the SELECT to see the profile in the results. Or add to the WHERE clause to filter for a specific profile.

Sean Lange Over a year ago

This works but is far less efficient that conditional aggregation because you have to read the same row over and over.

Collectives™ on Stack Overflow

How can I format an output table in SQL Server?

3 Answers 3

4 Comments

2 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related