January 13, 2017 at 2:56 am
Hi, I've got table that collect userinfo from custom inventory. I've been asked to return individual users for each machine and need some help with the query/function. I would like to create view with the data
so i can easily link to to other views/tables for my reporting.
Data I got currently is following (Sample data)
id logininfo
1 eu\denis|eu\dirk|eu\peter|eu\administrator
2 eu\joe|eu\dan|eu\dirk
I would like to return it in following format. Include filter to exclude 'administrator' no need for that user id
1 eu\denis
1 eu\dirk
1 eu\peter
2 eu\joe
2 eu\dan
2 eu\dirk
RDL info
CREATE TABLE #TEMPINFO (
id int,
logininfo varchar(max) )
INSERT INTO #TEMPINFO (id, logininfo) values (1, 'eu\denis|eu\dirk|eu\peter|eu\administrator')
INSERT INTO #TEMPINFO (id, logininfo) values (2, 'eu\joe|eu\dan|eu\dirk')
select * from #TEMPINFO
Thx for all help much appreciated.
January 13, 2017 at 4:32 am
John Mitchell-245523 - Friday, January 13, 2017 3:08 AMYou just need a splitter function. Take a look at this.John
Hi John thx for the info, I was also looking into the Split function but wanted to see if I could get this done in a view somehow vs. inserting this into table.
January 13, 2017 at 4:45 am
The splitter function is indeed a table-valued function, but that doesn't mean you have to use the results in a table. You can use it in a view if you like, something like this:CREATE VIEW dbo.SplitUsers AS
SELECT u.id, s.Item AS LoginName
FROM MyUsers u
CROSS APPLY dbo.DelimitedSplit8K(u.logininfo,'|') s
WHERE s.Item NOT LIKE '%administrator';
John
January 13, 2017 at 6:13 am
John Mitchell-245523 - Friday, January 13, 2017 4:45 AMThe splitter function is indeed a table-valued function, but that doesn't mean you have to use the results in a table. You can use it in a view if you like, something like this:CREATE VIEW dbo.SplitUsers AS
SELECT u.id, s.Item AS LoginName
FROM MyUsers u
CROSS APPLY dbo.DelimitedSplit8K(u.logininfo,'|') s
WHERE s.Item NOT LIKE '%administrator';John
thank you that did the trick. much appreciated.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply