August 19, 2016 at 10:26 pm
Hi,
I attach my Table and Data - sqlData-20082016-1.zip
How to create All Cross-Value based on data in Table Kumpulan_Wang? The result should be as
AA
AA,AA11
AA,CL
AA,ED
AA,FB
.....
.....
.....
AA,AA11,CL,ED,FB,FS,KA,PB
...
...
...
And so on till finish
...
...
I heard about Tally Table, Correlation Matrix. Unfortunately, I don't have any idea to write the SQL
Please help. Really need help
August 20, 2016 at 12:09 am
Quick questions for clarification:
1. Why are you doing this?
2. What is the end goal?
3. What is the cardinality of the table?
4. Should each combination be concatenated or in separate columns for each value?
5. Have you thought of the size of the result set (N!)
😎
August 20, 2016 at 12:09 am
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 20, 2016 at 1:22 am
Hello Guys,
Actually, I want computer to create Dynamic Parameter when User Select All Kumpulan_Wang(kwang_code). Without computer program, I have to write Dynamic Parameter by myself. The sample of parameter within All Kumpulan_Wang(kwang_code) are
AA
AA,AA11
AA,CL
AA,ED
AA,FB
.....
.....
.....
AA,AA11,CL,ED,FB,FS,KA,PB
...
...
...
And so on till finish all the combination of kwang_code
...
...
Please help me to built Dynamic kwang_code withing All Kumpulan_Wang(kwang_code). I hope all of you understood my requirement
August 20, 2016 at 1:45 am
Sorry, but I don't understand your requirement at all.
Please provide a COMPLETE set of data (scripted like you have) and a COMPLETE set of output(s) that you want from said data. Do not include anything that is not part of your expected output.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 20, 2016 at 2:00 am
My table and data as following,
USE [SPKU_DB_KR]
GO
/****** Object: Table [dbo].[Kumpulan_Wang_OUTPUT] Script Date: 20/8/2016 3:58:25 PM ******/
DROP TABLE [dbo].[Kumpulan_Wang_OUTPUT]
GO
/****** Object: Table [dbo].[Kumpulan_Wang] Script Date: 20/8/2016 3:58:25 PM ******/
DROP TABLE [dbo].[Kumpulan_Wang]
GO
/****** Object: Table [dbo].[Kumpulan_Wang] Script Date: 20/8/2016 3:58:25 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Kumpulan_Wang](
[idx] [int] IDENTITY(1,1) NOT NULL,
[disatu_code] [nvarchar](50) NULL,
[sequenceNo] [int] NULL,
[kwang_code] [nvarchar](50) NOT NULL,
[kwang_code_desc] [nvarchar](100) NULL,
CONSTRAINT [PK_Kumpulan_Wang] PRIMARY KEY CLUSTERED
(
[kwang_code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Kumpulan_Wang_OUTPUT] Script Date: 20/8/2016 3:58:25 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Kumpulan_Wang_OUTPUT](
[idx] [int] IDENTITY(1,1) NOT NULL,
[kwang_code_params] [nvarchar](500) NOT NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Kumpulan_Wang] ON
GO
INSERT [dbo].[Kumpulan_Wang] ([idx], [disatu_code], [sequenceNo], [kwang_code], [kwang_code_desc]) VALUES (11, N'01', 6, N'AA', N'Amanah')
GO
INSERT [dbo].[Kumpulan_Wang] ([idx], [disatu_code], [sequenceNo], [kwang_code], [kwang_code_desc]) VALUES (16, N'01', 10, N'AA11', N'Amanah INTEC')
GO
INSERT [dbo].[Kumpulan_Wang] ([idx], [disatu_code], [sequenceNo], [kwang_code], [kwang_code_desc]) VALUES (3, N'05', 15, N'CL', N'Pinjaman Kenderaan')
GO
SET IDENTITY_INSERT [dbo].[Kumpulan_Wang] OFF
GO
SET IDENTITY_INSERT [dbo].[Kumpulan_Wang_OUTPUT] ON
GO
INSERT [dbo].[Kumpulan_Wang_OUTPUT] ([idx], [kwang_code_params]) VALUES (1, N'AA')
GO
INSERT [dbo].[Kumpulan_Wang_OUTPUT] ([idx], [kwang_code_params]) VALUES (2, N'AA,AA11')
GO
INSERT [dbo].[Kumpulan_Wang_OUTPUT] ([idx], [kwang_code_params]) VALUES (3, N'AA,CL')
GO
INSERT [dbo].[Kumpulan_Wang_OUTPUT] ([idx], [kwang_code_params]) VALUES (4, N'AA11')
GO
INSERT [dbo].[Kumpulan_Wang_OUTPUT] ([idx], [kwang_code_params]) VALUES (5, N'AA11,CL')
GO
INSERT [dbo].[Kumpulan_Wang_OUTPUT] ([idx], [kwang_code_params]) VALUES (6, N'CL')
GO
SET IDENTITY_INSERT [dbo].[Kumpulan_Wang_OUTPUT] OFF
GO
We need built SQL from Kumpulan_Wang
Then, after having result - We need to insert into Kumpulan_Wang_OUTPUT. The expected result as in Kumpulan_Wang_OUTPUT table
Please help
August 20, 2016 at 2:15 am
To clarify, are you looking for a form of Consecutive Number Sequences, i.e. for the values 1-3 you would have the combinations below?
😎
1
12
123
13
2
23
3
August 20, 2016 at 2:50 am
Eirikur Eiriksson (8/20/2016)
To clarify, are you looking for a form of Consecutive Number Sequences, i.e. for the values 1-3 you would have the combinations below?😎
112
123
13
2
23
3
Yes Sir. But please make it format as below
kwang_code[1]
kwang_code[1],kwang_code[2]
kwang_code[1],kwang_code[2],kwang_code[3]
And so on
August 20, 2016 at 3:19 am
The way you describe it here does not fully match the initial requirements, can you please elaborate?
😎
Generating the output in the format described in your last post is easy, here is a quick example using your sample data set.
SELECT
STUFF((
SELECT DISTINCT
NCHAR(44) + NX.kwang_code AS [text()]
FROM dbo.Kumpulan_Wang NX
WHERE NX.kwang_code <= KW.kwang_code
FOR XML PATH('')
),1,1,N'') AS CAT_STR
FROM dbo.Kumpulan_Wang KW;
Output
CAT_STR
-------------------------------------------------------
AA
AA,AA11
AA,AA11,CL
AA,AA11,CL,ED
AA,AA11,CL,ED,FB
AA,AA11,CL,ED,FB,FS
AA,AA11,CL,ED,FB,FS,KA
AA,AA11,CL,ED,FB,FS,KA,KK
AA,AA11,CL,ED,FB,FS,KA,KK,KP
AA,AA11,CL,ED,FB,FS,KA,KK,KP,KY
AA,AA11,CL,ED,FB,FS,KA,KK,KP,KY,PB
AA,AA11,CL,ED,FB,FS,KA,KK,KP,KY,PB,PK
AA,AA11,CL,ED,FB,FS,KA,KK,KP,KY,PB,PK,PP
AA,AA11,CL,ED,FB,FS,KA,KK,KP,KY,PB,PK,PP,PP11
AA,AA11,CL,ED,FB,FS,KA,KK,KP,KY,PB,PK,PP,PP11,PS
AA,AA11,CL,ED,FB,FS,KA,KK,KP,KY,PB,PK,PP,PP11,PS,RD
AA,AA11,CL,ED,FB,FS,KA,KK,KP,KY,PB,PK,PP,PP11,PS,RD,SA
August 20, 2016 at 7:13 am
Hello Eirikur Eiriksson,
Why no string as follow?
AA11
AA11,CL
.................
......................
.............................
And so on?
August 20, 2016 at 10:20 am
If you had answered the questions properly and given appropriate examples then you would already have had a working solution by now.
😎
To achieve this, just add another instance to the table to scoop up the beginning values.
SELECT
STUFF((
SELECT DISTINCT
NCHAR(44) + NX.kwang_code AS [text()]
FROM dbo.Kumpulan_Wang NX
WHERE NX.kwang_code <= KW.kwang_code
AND NX.kwang_code >= KW2.kwang_code
FOR XML PATH('')
),1,1,N'') AS CAT_STR
FROM dbo.Kumpulan_Wang KW
CROSS JOIN dbo.Kumpulan_Wang KW2
WHERE KW2.kwang_code <= KW.kwang_code;
Output
CAT_STR
------------------------------------------------------------
AA
AA,AA11
AA,AA11,CL
AA,AA11,CL,ED
AA,AA11,CL,ED,FB
AA,AA11,CL,ED,FB,FS
AA,AA11,CL,ED,FB,FS,KA
AA,AA11,CL,ED,FB,FS,KA,KK
AA,AA11,CL,ED,FB,FS,KA,KK,KP
AA,AA11,CL,ED,FB,FS,KA,KK,KP,KY
AA,AA11,CL,ED,FB,FS,KA,KK,KP,KY,PB
AA,AA11,CL,ED,FB,FS,KA,KK,KP,KY,PB,PK
AA,AA11,CL,ED,FB,FS,KA,KK,KP,KY,PB,PK,PP
AA,AA11,CL,ED,FB,FS,KA,KK,KP,KY,PB,PK,PP,PP11
AA,AA11,CL,ED,FB,FS,KA,KK,KP,KY,PB,PK,PP,PP11,PS
AA,AA11,CL,ED,FB,FS,KA,KK,KP,KY,PB,PK,PP,PP11,PS,RD
AA,AA11,CL,ED,FB,FS,KA,KK,KP,KY,PB,PK,PP,PP11,PS,RD,SA
AA11
AA11,CL
AA11,CL,ED
AA11,CL,ED,FB
AA11,CL,ED,FB,FS
AA11,CL,ED,FB,FS,KA
AA11,CL,ED,FB,FS,KA,KK
AA11,CL,ED,FB,FS,KA,KK,KP
AA11,CL,ED,FB,FS,KA,KK,KP,KY
AA11,CL,ED,FB,FS,KA,KK,KP,KY,PB
AA11,CL,ED,FB,FS,KA,KK,KP,KY,PB,PK
AA11,CL,ED,FB,FS,KA,KK,KP,KY,PB,PK,PP
AA11,CL,ED,FB,FS,KA,KK,KP,KY,PB,PK,PP,PP11
AA11,CL,ED,FB,FS,KA,KK,KP,KY,PB,PK,PP,PP11,PS
AA11,CL,ED,FB,FS,KA,KK,KP,KY,PB,PK,PP,PP11,PS,RD
AA11,CL,ED,FB,FS,KA,KK,KP,KY,PB,PK,PP,PP11,PS,RD,SA
CL
CL,ED
CL,ED,FB
CL,ED,FB,FS
CL,ED,FB,FS,KA
CL,ED,FB,FS,KA,KK
CL,ED,FB,FS,KA,KK,KP
CL,ED,FB,FS,KA,KK,KP,KY
CL,ED,FB,FS,KA,KK,KP,KY,PB
CL,ED,FB,FS,KA,KK,KP,KY,PB,PK
CL,ED,FB,FS,KA,KK,KP,KY,PB,PK,PP
CL,ED,FB,FS,KA,KK,KP,KY,PB,PK,PP,PP11
CL,ED,FB,FS,KA,KK,KP,KY,PB,PK,PP,PP11,PS
CL,ED,FB,FS,KA,KK,KP,KY,PB,PK,PP,PP11,PS,RD
CL,ED,FB,FS,KA,KK,KP,KY,PB,PK,PP,PP11,PS,RD,SA
ED
ED,FB
ED,FB,FS
ED,FB,FS,KA
ED,FB,FS,KA,KK
ED,FB,FS,KA,KK,KP
ED,FB,FS,KA,KK,KP,KY
ED,FB,FS,KA,KK,KP,KY,PB
ED,FB,FS,KA,KK,KP,KY,PB,PK
ED,FB,FS,KA,KK,KP,KY,PB,PK,PP
ED,FB,FS,KA,KK,KP,KY,PB,PK,PP,PP11
ED,FB,FS,KA,KK,KP,KY,PB,PK,PP,PP11,PS
ED,FB,FS,KA,KK,KP,KY,PB,PK,PP,PP11,PS,RD
ED,FB,FS,KA,KK,KP,KY,PB,PK,PP,PP11,PS,RD,SA
FB
FB,FS
FB,FS,KA
FB,FS,KA,KK
FB,FS,KA,KK,KP
FB,FS,KA,KK,KP,KY
FB,FS,KA,KK,KP,KY,PB
FB,FS,KA,KK,KP,KY,PB,PK
FB,FS,KA,KK,KP,KY,PB,PK,PP
FB,FS,KA,KK,KP,KY,PB,PK,PP,PP11
FB,FS,KA,KK,KP,KY,PB,PK,PP,PP11,PS
FB,FS,KA,KK,KP,KY,PB,PK,PP,PP11,PS,RD
FB,FS,KA,KK,KP,KY,PB,PK,PP,PP11,PS,RD,SA
FS
FS,KA
FS,KA,KK
FS,KA,KK,KP
FS,KA,KK,KP,KY
FS,KA,KK,KP,KY,PB
FS,KA,KK,KP,KY,PB,PK
FS,KA,KK,KP,KY,PB,PK,PP
FS,KA,KK,KP,KY,PB,PK,PP,PP11
FS,KA,KK,KP,KY,PB,PK,PP,PP11,PS
FS,KA,KK,KP,KY,PB,PK,PP,PP11,PS,RD
FS,KA,KK,KP,KY,PB,PK,PP,PP11,PS,RD,SA
KA
KA,KK
KA,KK,KP
KA,KK,KP,KY
KA,KK,KP,KY,PB
KA,KK,KP,KY,PB,PK
KA,KK,KP,KY,PB,PK,PP
KA,KK,KP,KY,PB,PK,PP,PP11
KA,KK,KP,KY,PB,PK,PP,PP11,PS
KA,KK,KP,KY,PB,PK,PP,PP11,PS,RD
KA,KK,KP,KY,PB,PK,PP,PP11,PS,RD,SA
KK
KK,KP
KK,KP,KY
KK,KP,KY,PB
KK,KP,KY,PB,PK
KK,KP,KY,PB,PK,PP
KK,KP,KY,PB,PK,PP,PP11
KK,KP,KY,PB,PK,PP,PP11,PS
KK,KP,KY,PB,PK,PP,PP11,PS,RD
KK,KP,KY,PB,PK,PP,PP11,PS,RD,SA
KP
KP,KY
KP,KY,PB
KP,KY,PB,PK
KP,KY,PB,PK,PP
KP,KY,PB,PK,PP,PP11
KP,KY,PB,PK,PP,PP11,PS
KP,KY,PB,PK,PP,PP11,PS,RD
KP,KY,PB,PK,PP,PP11,PS,RD,SA
KY
KY,PB
KY,PB,PK
KY,PB,PK,PP
KY,PB,PK,PP,PP11
KY,PB,PK,PP,PP11,PS
KY,PB,PK,PP,PP11,PS,RD
KY,PB,PK,PP,PP11,PS,RD,SA
PB
PB,PK
PB,PK,PP
PB,PK,PP,PP11
PB,PK,PP,PP11,PS
PB,PK,PP,PP11,PS,RD
PB,PK,PP,PP11,PS,RD,SA
PK
PK,PP
PK,PP,PP11
PK,PP,PP11,PS
PK,PP,PP11,PS,RD
PK,PP,PP11,PS,RD,SA
PP
PP,PP11
PP,PP11,PS
PP,PP11,PS,RD
PP,PP11,PS,RD,SA
PP11
PP11,PS
PP11,PS,RD
PP11,PS,RD,SA
PS
PS,RD
PS,RD,SA
RD
RD,SA
SA
August 20, 2016 at 12:40 pm
Starting on the positive side: Thanks for posting a script to create tables and fill them with sample data. Very well done!
The negative side: I doubt wihether this should be done in SQL at all. It is very un-relational to try to stuff multiple values together in a single column. It can be done (SQL Server allows you to do a lot of things it is not designed for), but it will probably not scale well. There are almost certainly other tools much better fit for this problem.
However, I you really want to do this in T-SQL, then it can indeed be done. Unfortunately there is still some confusion on the expected output. The results in the Kumpulan_Wang_OUTPUT table in your post with sample data only has combinations of one or two values; the results in your original post (though incomplete) also include combinations of three or more values.
Based on your rerply to Eirikur, I now *think* that you want to have every possible combination of any number of values from the input table, in alphabetical order. If that is the correct interpretation, then the code Eirikur posted will not be the correct answer for you. His code is correct if you want every possible combination of *consecutive* values from the input data.
Here is my attempt:
WITH RecursiveCTE
AS (SELECT CAST(kw.kwang_code AS nvarchar(MAX)) AS kwang_code_params,
kw.kwang_code AS kwang_code_last
FROM dbo.Kumpulan_Wang AS kw
UNION ALL
SELECT rc.kwang_code_params + ',' + kw.kwang_code,
kw.kwang_code
FROM RecursiveCTE AS rc
INNER JOIN dbo.Kumpulan_Wang AS kw
ON kw.kwang_code > rc.kwang_code_last)
SELECT kwang_code_params
FROM RecursiveCTE;
Results on your test data:
AA
AA11
CL
AA11,CL
AA,AA11
AA,CL
AA,AA11,CL
(On the longer test data in the attachment to the first post of this topic, this same code produces 131071 rows - which makes me wonder if this is really what you want?)
August 20, 2016 at 3:27 pm
Hello Sir,
I will improve my communication
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply