TSQL: Need to have new column instead of next row

Question

Sorry if I asked in wrong way, I am new to SQL.

I have one temp table where I am inserting values after joining different tables

then,I came across where I need to update that temp table with 3 field( colm1 as datetime,colm2 datetime,colm3 money) for this i created field in temp table .

now,

  select colm1,colm2,sum(colm3)
  from OrginalTable
  where userid=@userid
  group by colm1,colm2

lets suppose this gives result like:

here after updating into Temp table, if multiple data then i need to do like below till 15 times:

Colm1,Com2,Colm3, colm1,colm2,colm3,

instead of creating new row I need this to be displayed as new field with in 1 row

execution result should be all in 1 row.

I am really confused whether I can do what is expected or not, if there is way please somebody give me examples or make it solution to this.

Lastly, appreciate your help

I'm sorry, this is too confusing, can you show what's your desired result?. `if multiple data then i need to do like below till 15 times` <- what does this mean?, why 15 times? — Lamak, Aug 30 '16 at 15:08
If you want new columns, I believe that you will have to select them. — Tim Biegeleisen, Aug 30 '16 at 15:12

score 1 · Answer 1 · answered Aug 30 '16 at 15:14

that sound like something you should do on your UI layer not for db.

Otherwise you need something like

 SELECT t1.*, t2.*, ....... , t15.*
 FROM yourTable as t1, 
      yourTable as t2, 
      ...... 
      yourTable as t15
 WHERE t1.col1 >= t2.col1 and t1.col2 > t2.col2
   AND t2.col1 >= t3.col1 and t2.col2 > t3.col2
   ....
   AND t14.col1 >= t14.col15 and t14.col2 > t15.col2

The problem is you need to know there are 15 rows, so if that number change your query wont work.

That is why you should do it on UI instead

Nope, this is done all alone through SQL. wish I could do through UI but again if so I have to make a UI — Akka, Aug 30 '16 at 15:34

score 1 · Accepted Answer · edited May 23 '17 at 10:29

You could use UNPIVOT and then Dynamic PIVOT

DEMO

Setup:

create table temp (
    col1 integer, col2 integer, col3 integer
)

insert into temp values (1, 1, 1000);
insert into temp values (1, 2, 500);
insert into temp values (2, 3, 800);
insert into temp values (2, 4, 700);
insert into temp values (3, 1, 1100);

UNPIVOT Query: You need a row_number to create a fake column name. But then overwrite the row to 1 for everyone, so all columns appear in a single row

WITH cte as (
    SELECT row_number() over (order by col1, col2) as rn,
           col1, col2, col3
    FROM temp
)
SELECT 1 as rn, 
       'c_' + 
       CAST(rn AS VARCHAR(16)) + '_' + 
       myCol as myCol, 
       myVal
INTO temp2            
FROM 
   (SELECT rn, col1, col2, col3
    FROM cte) p
UNPIVOT
   (myVal FOR myCol IN 
      (col1, col2, col3)
)AS unpvt;

SELECT * FROM temp2;

Dynamic Pivot:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.myCol) 
            FROM temp2 c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT rn, ' + @cols + ' from 
            (
                select rn
                    , myCol
                    , myVal
                from temp2
           ) x
            pivot 
            (
                max(myVal)
                for myCol in (' + @cols + ')
            ) p ';                    

EXEC sp_executesql @query

Output: Unpivot and Dynamic Pivot results

TSQL: Need to have new column instead of next row

2 Answers2