July 27, 2017 at 12:06 pm
l after inserting data to a table , i am facing difficulty to retrieve data as pivot.
declare @xml xml
set @xml = '<Report><row><value Description="Model">ABC49000</value><value Description="Quantity">12</value><value Description="Dealer_a1234">5</value><value Description="Dealer_a1234_Comission">2</value><value Description="Dealer_b1234">9</value><value Description="Dealer_b1234_Comission">3</value></row><row><value Description="Model">ABC44000</value><value Description="Quantity">12</value><value Description="Dealer_a1234" /><value Description="Dealer_a1234_Comission" /><value Description="Dealer_b1234" /><value Description="Dealer_b1234_Comission" /></row><row><value Description="Model">ABC45000</value><value Description="Quantity">12</value><value Description="Dealer_a1234" /><value Description="Dealer_a1234_Comission" /><value Description="Dealer_b1234">4</value><value Description="Dealer_b1234_Comission">2</value></row></Report>'
select @xml
IF OBJECT_ID('TempDB..#final', 'U') IS NOT NULL
drop table #final
Create table #final
(
Columnname varchar(250),
Value varchar(250)
)
insert into #final(Columnname,Value)
SELECT data.value('@Description', 'varchar(200)') AS [Description]
,data.value('.', 'varchar(200)') AS value
FROM @xml.nodes('/Report/row/value') x1([data])
where data.value('.', 'varchar(200)') <> ''
select * from #final
--o/p should be as below from #final table
select 'ABC49000' as Model ,12 as Quantity,5 as Dealer_a1234,2 as Dealer_a1234_Comission,9 as Dealer_b1234,3 as Dealer_b1234_Comission
union select 'ABC44000',12, '','','',''
union select 'ABC45000',12,'','',4,2
July 28, 2017 at 5:01 am
The problem here is your table set up, you lose any relationships between your data when you insert it into your table. This uses your XML directly instead, but gets the result you want:CREATE TABLE #XML (XmlColumn xml);
INSERT INTO #XML
SELECT @xml;
WITH XMLValues AS (
SELECT r.d.value('(value/text())[1]','varchar(50)') AS Model,
v.d.value('@Description','varchar(50)') AS Description,
v.d.value('(text())[1]','varchar(50)') AS Value
FROM #XML X
CROSS APPLY X.XmlColumn.nodes('/Report/row') r(d)
CROSS APPLY r.d.nodes('value') v(d)
)
SELECT Model, Quantity,
[Dealer_a1234],[Dealer_a1234_Comission], --Note, commission is spelt with a double m.
[Dealer_b1234],[Dealer_b1234_Comission] --Note, commission is spelt with a double m.
FROM (
SELECT Model, Description, ISNULL(CAST(value AS int),0) AS value
FROM XMLValues XV
WHERE XV.Description != 'model') XV
PIVOT
(SUM(value)
FOR Description IN ([Quantity],[Dealer_a1234],[Dealer_a1234_Comission],[Dealer_b1234],[Dealer_b1234_Comission]) --Note, commission is spelt with a double m.
) AS PT;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 28, 2017 at 8:55 am
thanks thom, i dont want to store null values in the table, may be if i introduce a sort order field it might help, but i was not able to generate a number to sort for each node.
July 28, 2017 at 9:28 am
mxy - Friday, July 28, 2017 8:55 AMthanks thom, i dont want to store null values in the table, may be if i introduce a sort order field it might help, but i was not able to generate a number to sort for each node.
Not sure what you mean, that SQL didn't generate any NULLs.
If on your real data it is, then your sample data didn't give enough scenarios. If you can provide more, then we can help further.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 28, 2017 at 9:49 am
i have a filter to remove any values that are blank in my original post. i have added code that i was able to achieve results by adding sort order, is it possible to generate from xml based on the node. let me if i miss any information
declare @xml xml
set @xml = '<Report><row><value Description="Model">ABC49000</value><value Description="Quantity">12</value><value Description="Dealer_a1234">5</value><value Description="Dealer_a1234_Comission">2</value><value Description="Dealer_b1234">9</value><value Description="Dealer_b1234_Comission">3</value></row><row><value Description="Model">ABC44000</value><value Description="Quantity">12</value><value Description="Dealer_a1234" /><value Description="Dealer_a1234_Comission" /><value Description="Dealer_b1234" /><value Description="Dealer_b1234_Comission" /></row><row><value Description="Model">ABC45000</value><value Description="Quantity">12</value><value Description="Dealer_a1234" /><value Description="Dealer_a1234_Comission" /><value Description="Dealer_b1234">4</value><value Description="Dealer_b1234_Comission">2</value></row></Report>'
select @xml
IF OBJECT_ID('TempDB..#final', 'U') IS NOT NULL
drop table #final
Create table #final
(
Columnname varchar(250),
Value varchar(250),
model varchar(150)
)
insert into #final(Columnname,Value,model)
SELECT v.d.value('@Description', 'varchar(200)') AS [Description]
,v.d.value('.', 'varchar(200)') AS value
,x1.data.value('(value/text())[1]','varchar(50)') AS Model
FROM @xml.nodes('/Report/row') x1([data])
CROSS APPLY x1.data.nodes('value') v(d)
where v.d.value('.', 'varchar(200)') <> ''
alter table #final add sortorder int
update #final
set sortorder = 1
where model = 'ABC49000' -- this is first node from xml i need to generate auotmatically, since model will be dynamic
update #final
set sortorder = 2
where model = 'ABC44000' -- this is 2nd node from xml i need to generate auotmatically, since model will be dynamic
update #final
set sortorder = 3
where model = 'ABC45000'-- this is 3rd node from xml i need to generate auotmatically, since model will be dynamic
select * from #final
SELECT MAX(CASE
WHEN Columnname = 'Model'
THEN value
END) AS 'Model'
,MAX(CASE
WHEN Columnname = 'Quantity'
THEN value
END) AS 'Quantity'
,MAX(CASE
WHEN Columnname = 'Dealer_a1234'
THEN value
END) AS 'Dealer_a1234'
,MAX(CASE
WHEN Columnname = 'Dealer_a1234_Comission'
THEN value
END) AS 'Dealer_a1234_Comission'
,MAX(CASE
WHEN Columnname = 'Dealer_b1234'
THEN value
END) AS 'Dealer_b1234'
,MAX(CASE
WHEN Columnname = 'Dealer_b1234_Comission'
THEN value
END) AS 'Dealer_b1234_Comission'
FROM #final t
GROUP BY sortorder
July 28, 2017 at 10:04 am
I'm really not sure what you're asking here, sorry. Could you elaborate further? Also, as I said, using your insert into your table #Final, it destroying any way to identify which record belongs to which. You'll need to create ID's at insertion or query the XML directly to get your data, like i did.
Why does the example I gave you not provide you with what you want, even though it replicates your expected output? You say it generates NULLs, but none are generated with the sample data you have provided, which implies I don't have the full picture.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply