SQLTeam.com | Weblogs | Forums

Column order using pivot


#1

I have this stored proc that uses pivot and as a result, the pivoted column (EMPLOYEE #:slight_smile: is the last column to show in the output. I want to change it so that the pivoted column now shows as the first one. How? Thanks.


With BaseQuery as (SELECT
CAST( CONVERT(VARBINARY,'0x'+RIGHT('00000000'+REPLACE(ch.CardCode,'x',''),8),1) AS INT) as CardCode,
CF.CustomFieldLabel, CFm.CustomFieldValue,
'Staff Card' as CardType
FROM table1.dbo.IDHolders id
inner join table2.[dbo].[CHCustomFieldMap] cfm on id.IDHolder = cfm.CardholderID
inner join table2.dbo.CHCustomFields cf on cf.CustomFieldID = cfm.CustomFieldID
inner join table2.dbo.CardHistory ch on ch.CardholderID = id.IDHolder
LEFT OUTER join table2.[dbo].[Cards] cd on cd.CardholderID = id.IDHolder
where CONVERT(date, ch.ReturnDate) BETWEEN CONVERT(date,DATEADD(day, -2,GETDATE())) AND CONVERT(date, Getdate())
) Select * from BaseQuery
pivot(MAX(CustomFieldValue)
for CustomFieldLabel in(
[EMPLOYEE #:]
)) pt


#2

Instead of using

Select * from BaseQuery

explicitly list the columns you want, e.g.

SELECT [EMPLOYEE #:], CardCode from BaseQuery