Beginners question about SQL statement

Transact-SQL
1

Dear SQL experts :slight_smile:

I have recently made a database to collect a lot of data and now I would like to present it in nice ways.

In order to do so I need to format my output in certain ways.

Say I have this data in my database:

ID n m

1 | a | e
1 | b | f
2 | c | e
2 | d | f

Is it then possible to construct an SQL statement which would give me this result:

1 | a | b
2 | c | d

Thanks in advance for your help...

2

FOR XML PATH is one approach:

select id, stuff(coln,1,1,'') as column_n
from mytable m1
cross apply ( 
	select '|' + n
	from mytable m2
	where m2.id = m1.id
	for xml path('')
) _(coln)

IF, OTOH you want separate columns, you'll need to use dynamic SQL, since there's not telling up front how many columns you will need

1 Like
3

To be more concrete then this is how my data looks like:

1.png728×255 22.7 KB

4

And this is how I would like it to look like:

Makes sense?

I need the data in this format in order to make some graphs with the data.

5

and...what if there are three rows for some date, or four? Even if there are not any now, there maybe in the future. That's why you probably want a dynamic SQL solution.

OTOH, if you can guarantee that there will always be exactly two rows, something like this would do ir:

select id, min(n) as one, max(n) as two
from mytable m1
group by id
1 Like
6

With min() and max() it seems that you select the lowest and the highest number.
In the above example it would work for all the lines apart from the one where "datofelt"="2" because it is empty for name="shot....again". So I guess the statement need to take into account the "name"...

7

Sorry for my ignorance but what is an dynamic SQL solution? Is it something not possible as 1 SQL statement?

8

Oh , that's you create a query as a string then execute it with the procedure sp_executesql

9

Try:

CREATE TABLE #Players(ID int IDENTITY PRIMARY KEY, Player varchar(50));
INSERT #Players
      ( Player )
VALUES
      ( 'shot...again' )      
   ,  ( 'Jakob' );

CREATE TABLE #Scores (ScoreDate date, PlayerId int, Score int, CONSTRAINT PK PRIMARY KEY ( ScoreDate, PlayerId ));
INSERT #Scores
      ( ScoreDate, PlayerId, Score )
VALUES
      ( '20161101' , 1  , 393  )      
   ,  ( '20161101' , 2  , 525  )
   ,  ( '20151231' , 2  , 2  )
   ,  ( '20161031' , 1  , 384  )
   ,  ( '20161031' , 2  , 510  )
   ,  ( '20161030' , 1  , 373  )
   ,  ( '20161030' , 2  , 479  )
   ,  ( '20161029' , 1  , 373  )
   ,  ( '20161029' , 2  , 471  );

SELECT ScoreDate, [shot...again], [Jakob]
FROM (
   SELECT ScoreDate, Player, Score
   FROM #Players p
   JOIN #Scores s ON p.ID = s.PlayerId
     ) Pl
PIVOT
   (
   Max(Score) FOR Player IN  ([shot...again], [Jakob])
   ) AS pvt
ORDER BY ScoreDate;

DROP TABLE #Players;
DROP TABLE #Scores;
1 Like
10 
IF OBJECT_ID('tempdb.dbo.#coc') IS NOT NULL
    DROP TABLE #coc;
CREATE TABLE #coc (datofelt date, name varchar(50), achievement22value int);
INSERT INTO #coc
VALUES( '20161101' , 'shot....again', 393  ),
      ( '20161101' , 'Jakob', 525  ),
      ( '20151231' , 'Jakob', 2 ),
      ( '20161031' , 'shot....again', 384  ),
      ( '20161031' , 'Jakob', 510  ),
      ( '20161030' , 'shot....again', 373  ),
      ( '20161030' , 'Jakob', 479  ),
      ( '20161029' , 'shot....again', 373  ),
      ( '20161029' , 'Jakob', 471  );

SELECT c.datofelt,
    MAX(CASE WHEN c.name = 'Jakob' THEN c.achievement22value END) AS [achievement22value (Jakob)],
    MAX(CASE WHEN c.name = 'shot....again' THEN c.achievement22value END) AS [achievement22value (shot...again)]
FROM #coc c
WHERE c.name IN ('Jakob', 'shot....again')
GROUP BY c.datofelt
2 Likes
11

Truely amazing ScottPletcher! It works as intended :slight_smile: Thank you so much! Really appreciate your help. You nailed it :slight_smile:

12

You're welcome! I'm glad it was helpful :slight_smile:

1 Like