March 30, 2021 at 3:31 pm
Hi,
current table result is below.
select FirstName,LastName,ID from emptable.
FirstName LastName ID
ABC DEF 34564
ABC DEF 23487989078128
I would like to display the result in single row like below based on len(id) column.
FirstName LastName ID1 ID2
ABC DEF 23487989078128 34564
Any suggestions.
Thank you.
March 30, 2021 at 3:37 pm
I would like to display the result in single row like below based on len(id) column.
What does 'based on' mean here?
Where are your sample DDL and INSERT statements?
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.
March 30, 2021 at 3:42 pm
I would like to display the result in single row like below .
FirstName LastName ID1 ID2
ABC DEF 23487989078128 34564
DDL:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Emptable](
[Firstname] [nvarchar](50) NULL,
[Lastname] [nvarchar](50) NULL,
[ID] [bigint] NULL
) ON [PRIMARY]
GO
--Insert statements
insert into emptable (Firstname,lastname,ID) values('ABC','DEF',34564)
insert into emptable (Firstname,lastname,ID) ('ABC','DEF',23487989078128)
March 30, 2021 at 4:00 pm
SELECT e.Firstname
,e.Lastname
,ID1 = MAX(e.ID)
,ID2 = MIN(e.ID)
FROM dbo.Emptable e
GROUP BY e.Firstname
,e.Lastname;
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.
March 30, 2021 at 4:02 pm
A query can only return a fixed number of columns. What is the maximum number of "ID" columns? ID5? ID100?
The code below will rank the ID columns from left to right according to their length. Add more "max(Case" lines up to the maximum number you need to hold all the strings for a given lastname/firstname.
;with cte as (select FirstName,Lastname, ID
,ColNo = Row_Number() over(Partition by FirstName,Lastname order by len(ID) desc)
from emptable
)
select Firstname, Lastname
,ID1 = max(case when ColNo = 1 then ID end)
,ID2 = max(case when ColNo = 2 then ID end)
,ID3 = max(case when ColNo = 3 then ID end)
from cte
group by firstname, lastname
order by Lastname, Firstname
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 30, 2021 at 4:05 pm
Thank you.
But i would like to use the function "len".
if the len(id) = 14 then it should be in ID1 otherwise it should be in ID2.
Can we achieve it ?
March 30, 2021 at 4:08 pm
Only two columns for ID.
if the len(id) = 14 then it should be in ID1 otherwise it should be in ID2.
March 30, 2021 at 4:16 pm
I just edited my post to give you the code based on len(ID). Hope it helps.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 30, 2021 at 4:17 pm
Thank you.
But i would like to use the function "len".
if the len(id) = 14 then it should be in ID1 otherwise it should be in ID2.
Can we achieve it ?
Unusual requirement, but yes:
SELECT e.Firstname
,e.Lastname
,ID1 = MAX(IIF(calcs.ln = 14, e.ID, NULL))
,ID2 = MAX(IIF(calcs.ln <> 14, e.ID, NULL))
FROM dbo.Emptable e
CROSS APPLY
(SELECT ln = LEN(CAST(e.ID AS VARCHAR(20)))) calcs
GROUP BY e.Firstname
,e.Lastname;
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.
March 30, 2021 at 7:05 pm
Thank you.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply