July 11, 2018 at 4:14 am
Hi Experts,
Need sql query help.
create table test
(
PropertyType varchar(100),
PropertyName varchar(100),
PropertyValue varchar(100),
)
insert into test
select 'TestType1','DisplayId','AUTO-TABLE'
union all
select 'TestType1','DisplayId','ID-OF-THE-QUESTION3'
union all
select 'TestType1','DisplayId','MANUAL-TABLE'
union all
select 'TestType1','Title','NEW BLANK QUESTION 1'
union all
select 'TestType1','Title','NEW BLANK QUESTION 2'
union all
select 'TestType1','Title','NEW BLANK QUESTION 3'
go
select * from test;
go
---Expected output
PropertyType DisplayId, Title
TestType1 AUTO-TABLE NEW BLANK QUESTION 1
TestType1 MANUAL-TABLE NEW BLANK QUESTION 2
TestType1 ID-OF-THE-QUESTION3 NEW BLANK QUESTION 3
Thanks,
Sam
July 11, 2018 at 5:28 am
vsamantha35 - Wednesday, July 11, 2018 4:14 AMHi Experts,Need sql query help.
create table test
(
PropertyType varchar(100),
PropertyName varchar(100),
PropertyValue varchar(100),
)insert into test
select 'TestType1','DisplayId','AUTO-TABLE'
union all
select 'TestType1','DisplayId','ID-OF-THE-QUESTION3'
union all
select 'TestType1','DisplayId','MANUAL-TABLE'
union all
select 'TestType1','Title','NEW BLANK QUESTION 1'
union all
select 'TestType1','Title','NEW BLANK QUESTION 2'
union all
select 'TestType1','Title','NEW BLANK QUESTION 3'
goselect * from test;
go
---Expected output
PropertyType DisplayId, Title
TestType1 AUTO-TABLE NEW BLANK QUESTION 1
TestType1 MANUAL-TABLE NEW BLANK QUESTION 2
TestType1 ID-OF-THE-QUESTION3 NEW BLANK QUESTION 3Thanks,
Sam
What have you tried so far? Looks like a straightforward cross-tab query.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 11, 2018 at 1:21 pm
select PropertyType,[DisplayId],[Title]
from (select ROW_NUMBER() over (partition by PropertyType,PropertyName order by PropertyValue) as rn,
PropertyType,
PropertyName,
PropertyValue
from test) as src
pivot ( max(PropertyValue) for PropertyName in ([DisplayId],[Title]) ) pvt
Is there other way?
July 11, 2018 at 1:41 pm
This query has already been answered elsewhere. I know this because I and at least one other person answered that other post. Here was my solution:CREATE TABLE #test (
PropertyType varchar(100),
PropertyName varchar(100),
PropertyValue varchar(100),
CONSTRAINT PK_TEMP_test_CLUST_IDX PRIMARY KEY CLUSTERED
(
PropertyType ASC,
PropertyName ASC,
PropertyValue ASC
)
);
INSERT INTO #test (PropertyType, PropertyName, PropertyValue)
SELECT 'TestType1','DisplayId','AUTO-TABLE' UNION ALL
SELECT 'TestType1','DisplayId','ID-OF-THE-QUESTION3' UNION ALL
SELECT 'TestType1','DisplayId','MANUAL-TABLE' UNION ALL
SELECT 'TestType1','Title','NEW BLANK QUESTION 1' UNION ALL
SELECT 'TestType1','Title','NEW BLANK QUESTION 2' UNION ALL
SELECT 'TestType1','Title','NEW BLANK QUESTION 3';
WITH ORDERED_DATA AS (
SELECT
T.*,
ROW_NUMBER() OVER(PARTITION BY T.PropertyType, T.PropertyName ORDER BY T.PropertyValue) AS RowNum
FROM #test AS T
)
SELECT
D1.PropertyType,
D1.PropertyValue AS DisplayId,
D2.PropertyValue AS Title
FROM ORDERED_DATA AS D1
INNER JOIN ORDERED_DATA AS D2
ON D1.PropertyType = D2.PropertyType
AND D1.RowNum = D2.RowNum
AND D1.PropertyName = 'DisplayId'
AND D2.PropertyName = 'Title'
ORDER BY D1.RowNum;
DROP TABLE #test;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 11, 2018 at 1:52 pm
It should be noted, that there's no way to guarantee the order of the rows and therefor the correct assignment of the pairs. At least with the data shown on the question.
July 11, 2018 at 2:57 pm
Luis Cazares - Wednesday, July 11, 2018 1:52 PMIt should be noted, that there's no way to guarantee the order of the rows and therefor the correct assignment of the pairs. At least with the data shown on the question.
I'm aware, but the data does appear in alpha order, and the query makes use of that fact. Also remember that this is a duplicate post....
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 11, 2018 at 4:23 pm
sgmunson - Wednesday, July 11, 2018 2:57 PMLuis Cazares - Wednesday, July 11, 2018 1:52 PMIt should be noted, that there's no way to guarantee the order of the rows and therefor the correct assignment of the pairs. At least with the data shown on the question.I'm aware, but the data does appear in alpha order, and the query makes use of that fact. Also remember that this is a duplicate post....
I'm sure that you are, but I'm not that sure about the OP or someone else trying to get advice.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply