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, Jan 30 '18 at 13:47
https://stackoverflow.com/questions/1605144/how-to-change-column-order-in-a-table-using-sql-query-in-sql-server-2005 — SQL_M, Jan 30 '18 at 13:48
@SQL_M that isnt the case here, he wants the data within the column to become the column — WhatsThePoint, Jan 30 '18 at 13:49
@Sami im not saying the question here isnt too broad, im just saying its not a dupe — WhatsThePoint, Jan 30 '18 at 13:51
Sql Server has PIVOT for this, I believe. Possible duplicate of https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server — Samar Rizvi, Jan 30 '18 at 13:53
Making rows to column better use PIVOT because UNPIVOT converts columns to rows. — coding Bott, Jan 30 '18 at 13:57
I see you've added what your source data looks like with the existing query - that's a start. Can you add what you'd like your output to look like as well? — user2366842, Jan 30 '18 at 14:00
You can use conditional aggregation for this pretty easily if you know ahead of time what columns you are using. If you don't know the columns you are going to be forced to use dynamic sql. — Sean Lange, Jan 30 '18 at 14:43

Aura · Answer 1 · 2018-01-30T14:19:46.810

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 '18 at 14:19

answered Jan 30 '18 at 14:06

Aura

1,283
2
16
30

thanks for that . Its giving me 2 rows with same title number. – Karan Neginhal Jan 30 '18 at 14:24
Add DISTINCT keyword after select, it should solve the duplication issue. – Aura Jan 30 '18 at 14:26
it still doesnt work .Its still giving me 2 rows with same title . Can you see my screenshot attached above ? – Karan Neginhal Jan 30 '18 at 14:29
You are on the right path here but you need aggregation to make this work correctly. See my answer. – Sean Lange Jan 30 '18 at 14:47

squillman · Answer 2 · 2018-01-30T14:50:26.547

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 '18 at 14:50

answered Jan 30 '18 at 14:38

squillman

13,363
3
41
60

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 – Karan Neginhal Jan 30 '18 at 14:44
Using this approach you will need to add additional JOINS and extend the WHERE clause. – squillman Jan 30 '18 at 14:46
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 ? – Karan Neginhal Jan 30 '18 at 15:39
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. – squillman Jan 30 '18 at 15:45
This works but is far less efficient that conditional aggregation because you have to read the same row over and over. – Sean Lange Jan 30 '18 at 16:51

score 0 · Answer 3 · answered Jan 30 '18 at 14:46

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 '18 at 14:46

Sean Lange

33,028
3
25
40

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" – Karan Neginhal Jan 30 '18 at 16:47
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. – Sean Lange Jan 30 '18 at 16:51

How can I format an output table in SQL Server?

3 Answers3