November 10, 2011 at 10:01 am
IDGroup A|Group B| Group C
1:True| False | True
2:False | False | True
3:True | True | True
TABLE B
This table ideally takes the values from above and squashes it in to one column only adding a value where the column / row has a value of true.
Groups
1:Group A; Group C;
2:Group C;
3:Group A; Group B; Group C;
I hope that made sense. If anyone can point me in the right direction or knows how to do it that would be fantastic.
Thanks for looking.
November 10, 2011 at 10:12 am
sc-w (11/10/2011)
IDGroup A|Group B| Group C1:True| False | True
2:False | False | True
3:True | True | True
TABLE B
This table ideally takes the values from above and squashes it in to one column only adding a value where the column / row has a value of true.
Groups
1:Group A; Group C;
2:Group C;
3:Group A; Group B; Group C;
I hope that made sense. If anyone can point me in the right direction or knows how to do it that would be fantastic.
Thanks for looking.
Keep it simple 🙂
BEGIN TRAN
CREATE TABLE #TABLE1 (ID INT, GroupA VARCHAR(10), GroupB VARCHAR(10), GroupC VARCHAR(10))
INSERT INTO #TABLE1
SELECT 1, 'True', 'False', 'True'
UNION ALL SELECT 2, 'False', 'False', 'True'
UNION ALL SELECT 3, 'True', 'True', 'True'
SELECT ID,
RTRIM(COALESCE(CASE WHEN GroupA = 'True'
THEN 'GroupA;'
ELSE NULL END + ' ', '') +
COALESCE(CASE WHEN GroupB = 'True'
THEN 'GroupB;'
ELSE NULL END + ' ', '') +
COALESCE(CASE WHEN GroupC = 'True'
THEN 'GroupC;'
ELSE NULL END + ' ', ''))
FROM #TABLE1
ROLLBACK
November 10, 2011 at 10:24 am
First lets set up some sample data:
IF OBJECT_ID('TempDB..#TableA') IS NOT NULL
DROP TABLE #TableA;
CREATE TABLE #TableA
( ID INT
,GroupA BIT
,GroupB BIT
,GroupC BIT
);
INSERT INTO #TableA
SELECT 1 , 'True' , 'False' ,'True'
UNION ALL SELECT 2,'False','False','True'
UNION ALL SELECT 3,'True','True','True' ;
Now the code to arrive at your results
;WITH UnPivotedValues AS
(
SELECT ID , Cols, TrueorFalse
FROM #TableA
UNPIVOT ( TrueorFalse FOR Cols IN ([GroupA],[GroupB],[GroupC]) ) UnPivotHandler
)
SELECT OutTab.ID, STUFF (( SELECT ','+Cols
FROM UnPivotedValues InTab
WHERE InTab.ID = OutTab.ID
AND InTab.TrueorFalse = 'True'
FOR XML PATH(''),TYPE ).value('.','VARCHAR(MAX)') , 1,1,SPACE(0)) AS TrueCols
FROM UnPivotedValues OutTab
GROUP BY OutTab.ID
November 10, 2011 at 10:27 am
Cadavre (11/10/2011)
Keep it simple 🙂
BEGIN TRANCREATE TABLE #TABLE1 (ID INT, GroupA VARCHAR(10), GroupB VARCHAR(10), GroupC VARCHAR(10))
INSERT INTO #TABLE1
SELECT 1, 'True', 'False', 'True'
UNION ALL SELECT 2, 'False', 'False', 'True'
UNION ALL SELECT 3, 'True', 'True', 'True'
SELECT ID,
RTRIM(COALESCE(CASE WHEN GroupA = 'True'
THEN 'GroupA;'
ELSE NULL END + ' ', '') +
COALESCE(CASE WHEN GroupB = 'True'
THEN 'GroupB;'
ELSE NULL END + ' ', '') +
COALESCE(CASE WHEN GroupC = 'True'
THEN 'GroupC;'
ELSE NULL END + ' ', ''))
FROM #TABLE1
ROLLBACK
Very good , cadavre. Single pass of the table. This will smoke my code.
November 10, 2011 at 12:21 pm
Amazing replies! Thank you both so much, I’ll work through both examples so that I understand the logic behind them. You’ve made my day tomorrow a lot better 😉 Thanks again
November 10, 2011 at 3:09 pm
Hi again, I should start by saying my SQL skills are poor at best.... Is it easy to have this script create the actual table instead of creating a virtual table?
Thanks in advance.
November 11, 2011 at 4:39 am
Hi Cadavre,
Is there any chance you could help me out with getting your example to insert into a psychical table called table2 with an id column and a column called result to capture the groupa; groupb etc?
I have tried but because i am limited in knowledge in this area i am getting all sorts of errors. I've tried adding an "INTO" in there but i just can't get it right.
Hope you can help. Thanks again.
November 11, 2011 at 4:49 am
SELECT INTO creates a new table. If you want to insert into an existing table, do it like this:
INSERT INTO Table1 (ColA, ColB, ...)
SELECT ColA, ColB, ...
FROM Table2
John
November 11, 2011 at 5:07 am
Thanks for the reply John,
I am tryin gthe insert part of what you suggested but no matter where i put it i get, loads of erros. Here is the code that gives me teh results in a virtual table.
BEGIN TRAN CREATE TABLE TABLE1(ID INT, GroupA VARCHAR(10), GroupB VARCHAR(10), GroupC VARCHAR(10))
INSERT INTO TABLE1
SELECT 1, 'True', 'False', 'True'
UNION ALL
SELECT 2, 'False', 'False', 'True'
UNION ALL
SELECT 3, 'True', 'True', 'True'
SELECT ID, RTRIM(COALESCE (CASE WHEN GroupA = 'True' THEN 'GroupA;' ELSE NULL END + ' ', '')
+ COALESCE (CASE WHEN GroupB = 'True' THEN 'GroupB;' ELSE NULL END + ' ', '')
+ COALESCE (CASE WHEN GroupC = 'True' THEN 'GroupC;' ELSE NULL END + ' ', ''))
FROM TABLE1
INSERT INTO Table2 (result)
ROLLBACK
Now i have created a table called table to with an id field that auto increments by one and a field called result where i want to capture the virtual reults.
I have used the following code and pasted in different parts of the above code without success. This is quite a complex script and i'm just grabbing at straws, where would you put it?
INSERT INTO Table2 (result)
Thanks for the help 🙂 Once i know i won't forget!
November 11, 2011 at 5:50 am
What goes into the parentheses at the end of the INSERT line is a list of the columns into which the rows will be inserted. If the columns in the SELECT statement are the same and in the same order as the columns in the table, you can omit the part in parentheses. You also need to move the INSERT line so that it comes immediately before the SELECT statement.
You'll find all this in Books Online, hopefully with a couple of helpful examples. Just search the internet for "SQL Server 2005 INSERT".
John
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply