CODEWITHSUNDEEP
sql-serversqlsql-server-2008
Anna
Anna

Reputation:

How do I make values from a SQL table be the headers of a View?

I have a table in SQL Server 2008 of the following sort:

Headings:  'ClientId'  'FieldName'      'Value'
Line1:     '1'         'Company Name'   'Company1'
Line2:     '1'         'Contract Type'  'Year'
Line3:     '1'         'Login Details'  '13546'
Line4:     '2'         'Company Name'   'Company2'
Line5:     '2'         'Contract Type'  'Month'
Line6:     '2'         'Login Details'  '46528'
Line7:     '3'         'Company Name'   'Company3'
Line8:     '3'         'Contract Type'  'Month'
Line9:     '3'         'Login Details'  '54687'

I need to create a View which would show the same information the following way:

Headings:  'ClientId'  'CompanyName'  'ContractType'  'Login'
Line1:     '1'         'Company1'     'Year'          '13546'
Line2:     '2'         'Company2'     'Month'         '46528'
Line3:     '3'         'Company3'     'Month'         '54687'

I can obtain the first two columns using the WHERE function choosing one particular FieldName value but cannot obtain the others in the same View. Another problem is that the output headers are not the same as in the source table. As I understand the SQL aliases has to be used here but I don't know how to apply them for this case.

How can I do this?

Upvotes: 1

Views: 87

Answers (2)

mehdi lotfi
mehdi lotfi

Reputation: 11601

You can use Pivot such as @Mikael Eriksson answer also can use below query :

SELECT  A.ClientID, 
        B.VALUE AS CompanyName, 
        C.VALUE AS ContractType, 
        D.VALUE AS [Login]
FROM    (SELECT DISTINCT ClientID FROM yourTable) A
LEFT JOIN YourTable AS B ON B.ClientID = A.ClientID AND B.FieldName = 'Company Name'
LEFT JOIN YourTable AS C ON B.ClientID = A.ClientID AND C.FieldName = 'Contract Type'
LEFT JOIN YourTable AS D ON B.ClientID = A.ClientID AND D.FieldName = 'Login Details'

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

You can use pivot.

select P.ClientId,
       P.[Company Name],
       P.[Contract Type],
       P.[Login Details]
from YourTable
pivot (min(Value) 
       for FieldName in ([Company Name],
                         [Contract Type],
                         [Login Details])
      ) P

SQL Fiddle

Upvotes: 2

Related Questions