Creating Columns from multiple values in rows

Question

Creating Columns from multiple values in rows

sqlstar2011

SSCertifiable

Points: 6000
More actions
October 31, 2018 at 9:20 am

#367616

One of the tables I am working with contains a column with string values like this:
3,4,6,9,12,24
1,6,9,12
1,2,3,4,5,6,7,8,9,10,11,12
These basically represent month terms.
I would like to create multiple columns for all the distinct value in the string in all rows like this:
1 - Month1
2 - Month2
3- Month3
and so on.
and then in case if a specific month is present for a string, the value for that in whatever column it maps to is 1 within that specific record else 0.
For example the record for Johnson looks like this:
it will transpose to this:
here Month3 is 1 because Johnson Term string contained 3. Month1 is 0 because it was not present in the Johnson's string.
Code:
create table #stringTranspose( Name varchar(10), Term varchar(30) )
insert into #stringTranspose values ('Johnson', '3,4,6,9,12,24') insert into #stringTranspose values ('DXB', '1,6,9,12') insert into #stringTranspose values ('LHR', '1,2,3,4,5,6,7,8,9,10,11,12') insert into #stringTranspose values ('SGP', '1,4,6,10,11,12,24')
End Result Example:
create table #stringTransposedTable( Name varchar(10), Term varchar(30), Month1 int, Month2 int, Month3 int, Month4 int, Month5 int, Month6 int, Month7 int, Month8 int, Month9 int, Month10 int, Month11 int, Month12 int, Month24 int )
insert into #stringTransposedTable values ('Johnson', '3,4,6,9,12,24', 0,0,1,1,0,1,0,0,1,0,0,1,1)
Any thoughts on how can this be accomplished?
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99130 More actions · Answer 1

I recommend against having numbered month columns. YOu should have a single column to denote the month number, and another to denote it's value. Like:

SELECT *
FROM (VALUES('Steve',1,1),
    ('Steve',2,0),
    ('Steve',3,1),
    --etc to 24
    ('Jayne',1,1),
    ('Jayne',2,1)
    --etc
  ) V([Name],MonthNo, Flag)

Repeating your column is breaking normal form, as it's going to cause you far more problems than it'll solve.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Thom A SSC Guru Points: 99130 More actions · Answer 2

On the note of getting your result set, I would do something like this:

WITH N AS(
  SELECT N
   FROM (VALUES(NULL),(NULL),(NULL)) N(N)),
Tally AS(
  SELECT TOP 24 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS I
   FROM N N1
    CROSS JOIN N N2
    CROSS JOIN N N3
   ORDER BY I),
Names AS(
   SELECT DISTINCT [Name]
   FROM #stringTranspose),
Split AS(
   SELECT ST.[Name],
     SS.[value]
   FROM #stringTranspose ST
   CROSS APPLY STRING_SPLIT(ST.Term,',') SS
  )
SELECT N.Name,
    T.I AS Term,
    CASE WHEN S.[value] IS NOT NULL THEN 1 ELSE 0 END AS Flag
FROM Tally T
   CROSS JOIN Names N
  LEFT JOIN Split S ON N.[Name] = S.[Name]
         AND T.I = S.[value]
ORDER BY N.[Name], T.I; 

Firstly I created a Tally list to get the numbers 1 - 24. Then, in the latter 2 CTE's, I returned a distinct list of names and also split the delimited data into rows. In the actually SELECT statement at the end, I cross joined the values from the tally and the distinct names CTEs to get every name with every month. Then I performed a LEFT JOIN to the split data, and returned 1 when there is a match, and 0 when there isn't.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

I agree with Thom. However, given that you have comedy limited values in your column(s), you're already in a bit of a pickle. If you absolutely have to do this, here's one way. One of the disadvantages of such denormalisation, of course, is that it relies on the string being perfectly formed. Any spaces or trailing commas, for example, and it might break.

SELECT Name , Term , CASE WHEN ',' + Term + ',' LIKE '%,1,%' THEN 1 ELSE 0 END AS Month1 , CASE WHEN ',' + Term + ',' LIKE '%,2,%' THEN 1 ELSE 0 END AS Month2 , CASE WHEN ',' + Term + ',' LIKE '%,3,%' THEN 1 ELSE 0 END AS Month3 , CASE WHEN ',' + Term + ',' LIKE '%,4,%' THEN 1 ELSE 0 END AS Month4 , CASE WHEN ',' + Term + ',' LIKE '%,5,%' THEN 1 ELSE 0 END AS Month5 , CASE WHEN ',' + Term + ',' LIKE '%,6,%' THEN 1 ELSE 0 END AS Month6 , CASE WHEN ',' + Term + ',' LIKE '%,7,%' THEN 1 ELSE 0 END AS Month7 , CASE WHEN ',' + Term + ',' LIKE '%,8,%' THEN 1 ELSE 0 END AS Month8 , CASE WHEN ',' + Term + ',' LIKE '%,9,%' THEN 1 ELSE 0 END AS Month9 , CASE WHEN ',' + Term + ',' LIKE '%,10,' THEN 1 ELSE 0 END AS Month10 , CASE WHEN ',' + Term + ',' LIKE '%,11%' THEN 1 ELSE 0 END AS Month11 , CASE WHEN ',' + Term + ',' LIKE '%,12%' THEN 1 ELSE 0 END AS Month12 , CASE WHEN ',' + Term + ',' LIKE '%,24%' THEN 1 ELSE 0 END AS Month24 FROM #stringTranspose

John

Thom A SSC Guru Points: 99130 More actions · Answer 4

John Mitchell-245523 - Wednesday, October 31, 2018 9:42 AM
However, given that you have comedy limited values in your column(s), you're already in a bit of a pickle.

That amused me (probably) far more than it should have done. :hehe:

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Thom A - Wednesday, October 31, 2018 9:52 AM
John Mitchell-245523 - Wednesday, October 31, 2018 9:42 AM
However, given that you have comedy limited values in your column(s), you're already in a bit of a pickle.
That amused me (probably) far more than it should have done. :hehe:

Comedy limited? I think I got that off of Phil Factor back in the day.

By the way, I'll leave the reader to observe the (not-so) deliberate errors in the last four CASE expressions in my code.

John

sqlstar2011 SSCertifiable Points: 6000 More actions · Answer 6

Thanks John and Thom. - I will try both solutions and report back.