May 24, 2016 at 12:57 am
I have database link follow
http://www.4shared.com/file/UXrVFpVvba/Example.html
in database have 3 table (cap2,TB_CongVan,TB_Result)
i want use procedure or function combination of data between two tables cap2 and TB_CongVan is return result in TB_Result
Help me?
Thanks
May 24, 2016 at 6:30 am
There are four different buttons for downloading on that site. It's unclear which one is for actually downloading and which one(s) are going to launch some sort of virus. So I didn't download your file. Instead, could you just post the DDL query that creates the tables? I, or someone else, can make a suggestion based on that. I'm not sure anyone is going to want to click on those sketchy looking links.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 24, 2016 at 7:44 pm
Thanks for reply!
Sorry for the inconvenience
This is script
USE [Example]
GO
/****** Object: Table [dbo].[TB_Result] Script Date: 05/25/2016 08:30:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TB_Result](
[idCV] [int] IDENTITY(1,1) NOT NULL,
[SoCV] [nvarchar](50) NULL,
[ReadUser] [nvarchar](max) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[TB_CongVan] Script Date: 05/25/2016 08:30:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TB_CongVan](
[idCV] [int] IDENTITY(1,1) NOT NULL,
[SoCV] [nvarchar](250) NULL,
[ReadUser] [nvarchar](max) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[cap2] Script Date: 05/25/2016 08:30:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[cap2](
[id] [int] IDENTITY(1,1) NOT NULL,
[ten] [nvarchar](250) NULL,
[SoCV] [nvarchar](250) NULL
) ON [PRIMARY]
GO
and this is database
table cap2
INSERT INTO cap2(ten,SoCV) VALUES('user1|user2|user3','922/QD-HDQT-TDHO')
INSERT INTO cap2(ten,SoCV) VALUES('user1|user2','85-TB/BCSÐ-NHNN')
INSERT INTO cap2(ten,SoCV) VALUES('user1|user2|user3','286/CD-NHNO')
INSERT INTO cap2(ten,SoCV) VALUES('user1|user2|user3','388/QD/NHNO-TP-HCNS')
INSERT INTO cap2(ten,SoCV) VALUES('user4|user5','922/QD-HDQT-TDHO')
table TB_CongVan
INSERT INTO TB_CongVan(SoCV, ReadUser) VALUES('922/QD-HDQT-TDHO','a|b|c')
INSERT INTO TB_CongVan(SoCV, ReadUser) VALUES('85-TB/BCSÐ-NHNN','a|b')
INSERT INTO TB_CongVan(SoCV, ReadUser) VALUES('286/CD-NHNO','a|b|c|d')
INSERT INTO TB_CongVan(SoCV, ReadUser) VALUES('388/QD/NHNO-TP-HCNS','a|b|c')
return result table TB_Result
INSERT INTO TB_Result(SoCV, ReadUser) VALUES('922/QD-HDQT-TDHO','a|b|c|user1|user2|user3|user4|user5')
INSERT INTO TB_Result(SoCV, ReadUser) VALUES('85-TB/BCSÐ-NHNN','a|b|user1|user2')
INSERT INTO TB_Result(SoCV, ReadUser) VALUES('286/CD-NHNO','a|b|c|d|user1|user2|user3')
INSERT INTO TB_Result(SoCV, ReadUser) VALUES('388/QD/NHNO-TP-HCNS','a|b|c|user1|user2|user3')
May 24, 2016 at 11:39 pm
I don't agree with storing values as separated lists.
But, to answer your question, I have made the assumption that TB_CongVan is the parent table, and cap2 is the child table.
SELECT
cv.SoCV
, ReadUser = cv.ReadUser + ISNULL('|' + cap.ReadUser, '')
FROM dbo.TB_CongVan AS cv
LEFT JOIN (
SELECT
SoCV
, ReadUser = LTRIM(RTRIM(STUFF((
SELECT '|' + d.ten
FROM dbo.cap2 AS d
WHERE c.SoCV = d.SoCV
ORDER BY d.id
FOR XML PATH('')
), 1, 1, '')))
FROM dbo.cap2 AS c
GROUP BY c.SoCV
) AS cap
ON cv.SoCV = cap.SoCV;
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply