May 2, 2002 at 3:40 pm
Hi, I need some help on constructing a sql to format my string.
Here is the problem:
I have a table
my_table
------------------------------------
user_id (numeric(8,0) )
f1 varchar(100),
f2 varchar(100),
f3 varchar(100),
f4 varchar(100),
f5 varchar(100)
The table's data is like this:
user_id, f1, f2, f3, f4, f5
1, good, bad, O.K., bad, good
1, bad, good, normal, good, bad
1, excellent, bad, o.k, bad, good
2, good, bad, o.k., bad, good
2, excellent, bad, o.k, bad, good
2, normal, bad, o.k. bad, good
...
Each user_id has 3 rows in the table.
What I need to achieve is to concat the 3 rows per user_id together by column, or:
set @mv_string = f1 + f1 + f1 + f2 + f2 + f2
+ f3 + f3+ f3 + f4 + f4 + f4 + f5 + f5 + f5
per user_id.
I have no problem to do this based on one user_id 3 rows:
@mv_string = f1 + f2 + f3 + f4 + f5 + f1 + f2 + f3 + f4 + f5 + f1 + f2 + f3 + f4 + f5
But so far I can't figure out how to concat
the 3 rows based on column.
Any good idea?
Thanks in advance.
Abby
May 3, 2002 at 5:50 am
Try this, based on what you have provided this is the only possiblity I see.
SET NOCOUNT ON
CREATE TABLE xtext (
[user_id] [numeric](8, 0) NULL ,
[f1] [varchar] (100) NULL ,
[f2] [varchar] (100) NULL ,
[f3] [varchar] (100) NULL ,
[f4] [varchar] (100) NULL ,
[f5] [varchar] (100) NULL
) ON [PRIMARY]
GO
INSERT INTO xtext ([user_id],f1,f2,f3,f4,f5) VALUES(1, 'good', 'bad', 'o.k.', 'bad', 'good')
INSERT INTO xtext ([user_id],f1,f2,f3,f4,f5) VALUES(1, 'bad', 'good', 'normal', 'good', 'bad')
INSERT INTO xtext ([user_id],f1,f2,f3,f4,f5) VALUES(1, 'excellent', 'bad', 'o.k.', 'bad', 'good')
INSERT INTO xtext ([user_id],f1,f2,f3,f4,f5) VALUES(2, 'good', 'bad', 'o.k.', 'bad', 'good')
INSERT INTO xtext ([user_id],f1,f2,f3,f4,f5) VALUES(2, 'excellent', 'bad', 'o.k.', 'bad', 'good')
INSERT INTO xtext ([user_id],f1,f2,f3,f4,f5) VALUES(2, 'normal', 'bad', 'o.k.', 'bad', 'good')
GO
--------------------The code that does the work----------------------------
SET NOCOUNT ON
CREATE TABLE #xtext (
[idx] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
[user_id] [numeric](8, 0) NULL ,
[f1] [varchar] (100) NULL ,
[f2] [varchar] (100) NULL ,
[f3] [varchar] (100) NULL ,
[f4] [varchar] (100) NULL ,
[f5] [varchar] (100) NULL
)
INSERT INTO #xtext ([user_id],f1,f2,f3,f4,f5) SELECT * FROM xtext ORDER BY [user_id]
SELECT
fItem.[user_id],
ISNULL(fItem.f1,'') + ', ' + ISNULL(sItem.f1,'') + ', ' + ISNULL(tItem.f1,'') + ', ' +
ISNULL(fItem.f2,'') + ', ' + ISNULL(sItem.f2,'') + ', ' + ISNULL(tItem.f2,'') + ', ' +
ISNULL(fItem.f3,'') + ', ' + ISNULL(sItem.f3,'') + ', ' + ISNULL(tItem.f3,'') + ', ' +
ISNULL(fItem.f4,'') + ', ' + ISNULL(sItem.f4,'') + ', ' + ISNULL(tItem.f4,'') + ', ' +
ISNULL(fItem.f5,'') + ', ' + ISNULL(sItem.f5,'') + ', ' + ISNULL(tItem.f5,'') As Cated
FROM
(SELECT * FROM #xtext WHERE (idx % 3) - 1 = 0) as fItem
INNER JOIN
(SELECT * FROM #xtext WHERE (idx % 3) - 2 = 0) as sItem
ON
fItem.[user_id] = sItem.[user_id]
INNER JOIN
(SELECT * FROM #xtext WHERE (idx % 3) = 0) as tItem
ON
sItem.[user_id] = tItem.[user_id]
DROP TABLE #xtext
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 3, 2002 at 3:47 pm
Thank you very much.
Abby
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply