to display the column to be logid, (the list of columnNames as Column Names) with the results being the data in B4
In this example it would be one row of results
I tried this and get an error incorrect syntax near the keyword "for'.
Select *
from
(
select i.ColumnName, i.Logid, i.Before as B4
from COLOGITM i
where i.LogId=8324518
) SourceTable
PIVOT
(B4 for ColumnName IN (LogId)
) as PivotTable
Your getting close, you need to specify the columns and B4 is an aggregate, like SUM, MAX, MIN, AVG etc..
I always try to start small when I try something new without actual hitting the database, something like this:
DROP TABLE IF EXISTS #CardGunner
SELECT
CONVERT(VARCHAR(35),'DateUpdate') AS ColumnName, CONVERT(INT,8324518) AS Logid, CONVERT(VARCHAR(250),'2023-08-11 11:04:17.997') AS [Before]
INTO #CardGunner
UNION
SELECT 'DateCreate' ,8324518,'2023-08-07 13:52:03.000'
UNION
SELECT 'EmpIdSpn', 8324518, '1157'
UNION
SELECT 'EmpId', 8324518, '1157';
SELECT
p.LogID,
[DateUpdate],
[DateCreate],
[EmpIdSpn],
[EmpID]
FROM
(
SELECT i.ColumnName, i.Logid, i.[Before] AS B4
FROM #CardGunner i
WHERE i.LogId=8324518
) tbl
PIVOT
(MAX(B4) FOR [ColumnName] IN ([DateUpdate],[DateCreate],[EmpIdSpn],[EmpID])
) AS p;
hope this helps and this is what you are looking for
i used dynamic pivot
create data script
drop table #data
create table #data (ColumnName varchar(30) , Logid int , B4 varchar(40))
insert into #data select 'DateUpdate',8324518,'2023-08-11 11:04:17.997'
insert into #data select 'DateCreate',8324518,'2023-08-07 13:52:03.000'
insert into #data select 'EmpIdSpn',8324518,'1157'
insert into #data select 'EmpId',8324518,'1157'
insert into #data select 'TaxId',8324518,'EX'
insert into #data select 'QtyBackorder',8324518,'0.00'
insert into #data select 'QtyShip',8324518,'4.00'
insert into #data select 'AmtRebate',8324518,'0.00'
insert into #data select 'AmtCost',8324518,'73.95'
insert into #data select 'AmtPriceSuggest',8324518,'114.23'
insert into #data select 'AmtPrice',8324518,'114.23'
insert into #data select 'Reason',8324518,'DAVID FORRESTER.T201007948.4952'
insert into #data select 'ClaId',8324518,'428'
insert into #data select 'Des',8324518,'AUTOMATIC SLACK ADJUSTOR *D'
insert into #data select 'PtItm',8324518,'103FTBW K041771'
insert into #data select 'ItmTyp',8324518,'528'
insert into #data select 'SlsId',8324518,'X103054320'
insert into #data select 'ItmId',8324518,'4370501'
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT distinct ',max(' + QUOTENAME(c.B4) +')' FROM #data c FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')
SET @query = 'SELECT Logid, ' + @cols + ' from (select * from #data ) x
pivot
( max(B4) for B4 in (' + replace(replace(replace(@cols,'max',''),')',''),'(','') + ') ) p group by LogId '
EXEC (@query)
I get this error after replacing #data with my table names
Msg 6841, Level 16, State 1, Line 4
FOR XML could not serialize the data for node 'NoName' because it contains a character (0x0001) which is not allowed in XML. To retrieve this data using FOR XML, convert it to binary, varbinary or image data type and use the BINARY BASE64 directive.
