Cross Apply / Pivot this data?

Question

I have the following data:

AutoIndex	Description	InvDate	UserValue	cFieldName
561498	Sales Order	18/10/2019	Normal	ulIDSOrdImportance
561498	Sales Order	18/10/2019	0	ufIDSOrdKilometres
561498	Sales Order	18/10/2019	16T Truck	ulIDSOrdTruckType
561498	Sales Order	18/10/2019	B	ulIDSOrdArea
561498	Sales Order	18/10/2019	lucky	ucIDSOrdSiteContactName
561498	Sales Order	18/10/2019	8997472	ucIDSOrdSiteTelephone

With the following script, I'm trying to cross apply the data:

select
i.AutoIndex
,   i.Description
,   convert(date,InvDate)   InvDate
,   h.UserValue
,   u.cFieldName
,   ul.*
from    InvNum              i
join    _etblUserHistLink   h   on  i.AutoIndex     =   h.TableID
join    _rtblUserDict       u   on  h.UserDictID    =   u.idUserDict
cross apply
(
    values  (cFieldName,UserValue)
)   ul (col,fiel)

Which gives me this results:

The respected results I require is as following:

What am I doing wrong?

I'm not sure I follow what you're asking here. What do you mean by you want to "cross apply" the data? What is your `VALUES` table construct trying to achieve here, as it's simply duplicating 2 columns. If you want to pivot your data then you want `PIVOT` or conditional aggregation. `VALUES` is used to unpivot your data (instead of `UNPIVOT`). Pivoting data "turn rows into column", where as unpivoting "converts columns to rows". — Thom A, Jan 26 '21 at 15:11
Yes, I think Pivot would work, I hit a blank. Basically what I need is to "transpose" the data as you would do it in Excel... — Attie Wagner, Jan 26 '21 at 15:12
No, it doesn't need to be dynamic @Larnu. Do you perhaps have an answer for me? I can't work out how a pivot would work (because there is nothing to sum) — Attie Wagner, Jan 26 '21 at 15:17
There are other aggregate function, @Birel, such as `MAX` (which is often used during pivoting) — Thom A, Jan 26 '21 at 15:17
Does this answer your question? [Efficiently convert rows to columns in sql server](https://stackoverflow.com/q/15745042/2029983) — Thom A, Jan 26 '21 at 15:18
Yes it does @Larnu. I'll close this question or you can answer if you would like — Attie Wagner, Jan 26 '21 at 15:19
I can "gold badge" it, no worries. :) *Wait, no I can't, doesn't have sql, or SQL server tags, and i'm 5 score from being able to do it on T0SQL, nooo!!!* Lol, You can accept as the dupe option though. — Thom A, Jan 26 '21 at 15:20
I'll add the tags or you can edit my question, then add the tags @Larnu? :) — Attie Wagner, Jan 26 '21 at 15:22
You'll be able to accept the dupe candidate now I'm marked it as a dupe, @Birel. I can't add tags and then "gold badge"; it gives me a little warning to tell me I'm not allowed to. :) There'll be a message at the top about accepting the dupe candidate, and the [Community♦](https://stackoverflow.com/users/-1/community) will close it for you instead. — Thom A, Jan 26 '21 at 15:23

Cross Apply / Pivot this data?

0 Answers0