Help with a SQL Unpivot
-
I am trying to use unpivot to extract some data, however, the results are getting messed up on at least one record. By this I mean it is out of order which it should be
CLNUM:
MODEL:
CLSTATUS:
CLSTATDATE:
CLSTOP:
Here is the query any help or advice would be appreciated.
WITH ClientsWithOpenMattersCTE (ClientNo, HowMany)
AS (select c.clnum,
--, m.mstatus
count(*) as HowMany
from client c
join matter m
on c.clnum = m.mclient
where m.mstatus = 'OP'
group by c.clnum,
m.mstatus
)
,Summary
as (
select [clnum] as CLNUM,
clnum as MODEL,
'F' as CLSTATUS,
CLSTATDATE = Convert(varchar, dateadd(d, 365, lastclosedate), 103),
'Y' as CLSTOP
FROM
(
select c.clnum,
c.clname1,
max(m.mclosedt) as LastCloseDate
from client c
left outer join ClientsWithOpenMattersCTE cte
on c.clnum = cte.ClientNo
join matter m
on c.clnum = m.mclient
where
1=1
and cte.ClientNo is null
group by c.clnum,
c.clname1
) X
--order by clnum
)
,
CTE_FINAL as (
Select
Cast(CLNUM as nvarchar(50)) as [CLNUM:]
,cast(Model as nvarchar(50)) as [Model:]
,cast(CLSTATUS as nvarchar(50)) as [Clstatus:]
,cast(CLSTATDATE as nvarchar(50)) as [CLSTATDATE:]
,cast(CLSTOP as nvarchar(50)) as [CLSTOP:]
from Summary) ---This CTE is required to get the output into a format that can be unpivoted
SELECT Category,
[Data]
FROM
(SELECT [CLNUM:], [MODEL:], [CLSTATUS:], [CLSTATDATE:], [CLSTOP:] from CTE_FINAL) p UNPIVOT([Data] for Category IN([CLNUM:], [MODEL:], [CLSTATUS:], [CLSTATDATE:], [CLSTOP:])) AS upvt;- This topic was modified 1 year, 6 months ago by rogue1009.
-
By this I mean it is out of order which it should be
Please take some time to think about people reading your post. We have no idea ... none at all ... what you mean by this.
If, however, you take the time to post DDL and INSERT statements with sample data and show desired results based on that data, someone will surely help you out.
It would also be helpful if you would ask a question, rather than hoping that we can figure it out from your 'I am trying' statement.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Sorry, about that this has me really confused and should have explained it better.
Ok so my table of data looks like the below
I need my query to output to this format
The unpivot part of my query seems to output at least one record out of sequence. Is there something wrong with this part of the query:
SELECT Category,
[Data]
FROM
(SELECT [CLNUM:], [MODEL:], [CLSTATUS:], [CLSTATDATE:], [CLSTOP:] from CTE_FINAL) p UNPIVOT([Data] for Category IN([CLNUM:], [MODEL:], [CLSTATUS:], [CLSTATDATE:], [CLSTOP:])) AS upvt;
Or is there something else i could try instead of unpivot.
-
Maybe this?
DROP TABLE IF EXISTS #Data;
CREATE TABLE #Data
(
CLNUM INT
,Model INT
,CLSTATUS CHAR(1)
,CLSTATDATE DATE
,CLSTOP CHAR(1)
);
INSERT #Data
(
CLNUM
,Model
,CLSTATUS
,CLSTATDATE
,CLSTOP
)
VALUES
(1, 1, 'F', '20030207', 'Y')
,(2, 2, 'F', '20041204', 'Y')
,(3, 3, 'F', '19910816', 'Y')
,(4, 4, 'F', '19960118', 'Y')
,(5, 5, 'F', '20000215', 'Y');
SELECT c1.*
FROM #Data d
CROSS APPLY
(
SELECT Category = 'CLNUM:'
,Data = CAST (d.CLNUM AS VARCHAR(50))
UNION ALL
SELECT 'MODEL:'
,CAST (d.Model AS VARCHAR(50))
UNION ALL
SELECT 'CLSTATUS:'
,CAST (d.CLSTATUS AS VARCHAR(50))
UNION ALL
SELECT 'CLSTATDATE:'
,CAST (d.CLSTATDATE AS VARCHAR(50))
UNION ALL
SELECT 'CLSTOP:'
,CAST (d.CLSTOP AS VARCHAR(50))
) c1
ORDER BY d.CLNUM;The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel - Phil Parkin wrote:
Maybe this?
DROP TABLE IF EXISTS #Data;
CREATE TABLE #Data
(
CLNUM INT
,Model INT
,CLSTATUS CHAR(1)
,CLSTATDATE DATE
,CLSTOP CHAR(1)
);
INSERT #Data
(
CLNUM
,Model
,CLSTATUS
,CLSTATDATE
,CLSTOP
)
VALUES
(1, 1, 'F', '20030207', 'Y')
,(2, 2, 'F', '20041204', 'Y')
,(3, 3, 'F', '19910816', 'Y')
,(4, 4, 'F', '19960118', 'Y')
,(5, 5, 'F', '20000215', 'Y');
SELECT c1.*
FROM #Data d
CROSS APPLY
(
SELECT Category = 'CLNUM:'
,Data = CAST (d.CLNUM AS VARCHAR(50))
UNION ALL
SELECT 'MODEL:'
,CAST (d.Model AS VARCHAR(50))
UNION ALL
SELECT 'CLSTATUS:'
,CAST (d.CLSTATUS AS VARCHAR(50))
UNION ALL
SELECT 'CLSTATDATE:'
,CAST (d.CLSTATDATE AS VARCHAR(50))
UNION ALL
SELECT 'CLSTOP:'
,CAST (d.CLSTOP AS VARCHAR(50))
) c1
ORDER BY d.CLNUM;Very good.
It can be made a bit more succinctly using values instead of select:
SELECT c1.*
FROM #Data d
CROSS APPLY(VALUES ('CLNUM:', CAST(d.CLNUM AS VARCHAR(50))),
('MODEL:', CAST(d.Model AS VARCHAR(50))),
('CLSTATUS:', CAST(d.CLSTATUS AS VARCHAR(50))),
('CLSTATDATE:', CAST(d.CLSTATDATE AS VARCHAR(50))),
('CLSTOP:', CAST(d.CLSTOP AS VARCHAR(50)))) c1(Category,Data)
ORDER BY d.CLNUM
; -
Believe this is now sorted, thanks Phil
-
Nice tweak, that's much cleaner. Thanks, Jonathan.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply