CODEWITHSUNDEEP
sql-servert-sqlpivot
Sunny Sandeep
Sunny Sandeep

Reputation: 1011

How to set rows value as column in SQL Server?

I have a table tblTags (Date, Tagindex, Value)

The values in the table are:

Date            Tagindex    Value
---------------------------------
2017-10-21         0          21
2017-10-21         1         212
2017-10-21         2          23
2017-10-21         0          34
2017-10-21         1          52
2017-10-21         2          65

I want the result as :

Date            0    1     2
-------------------------------
2017-10-21      21   212   23
2017-10-21      34    52   65

For this I wrote the followring query

select * 
from
    (SELECT a.Date, a.Tagindex,a.value
     FROM tblTag a) as p
pivot 
    (max(value)
        for Tagindex in ( [tblTag])
    ) as pvt

But I get these errors:

Msg 8114, Level 16, State 1, Line 10
Error converting data type nvarchar to int.

Msg 473, Level 16, State 1, Line 10
The incorrect value "tblTag" is supplied in the PIVOT operator.

How to solve this issue.

Upvotes: 1

Views: 1234

Answers (2)

shA.t
shA.t

Reputation: 16968

I think can use a query like this:

;with t as (
  select *
    , row_number() over (partition by [Date],[Tagindex] order by (select 0)) seq
  from tblTag
)
select [Date],
  max(case when [Tagindex] = 0 then [Value] end) '0',
  max(case when [Tagindex] = 1 then [Value] end) '1',
  max(case when [Tagindex] = 2 then [Value] end) '2'
from t
group by [Date], seq;

SQL Server Fiddle Demo
SQL Server Fiddle Demo - with pivot

Note: In above query I use row_number() function to create a sequence number for each Date and Tagindex, But the trick is in using (select 0) that is a temporary field to use in order by part, that will not trusted to return arbitrary order of inserted rows.So, if you need to achieve a trusted result set; you need to have an extra field like a datetime or an auto increment field.

Upvotes: 1

gotqn
gotqn

Reputation: 43636

Try this:

DECLARE @tblTag TABLE
(
    [Date] DATE
   ,[TagIndex] TINYINT
   ,[Value] INT
);

INSERT INTO @tblTag ([Date], [TagIndex], [Value])
VALUES ('2017-10-21', 0, 21)
      ,('2017-10-21', 1, 212)
      ,('2017-10-21', 2, 23)
      ,('2017-10-22', 0, 34)
      ,('2017-10-22', 1, 52)
      ,('2017-10-22', 2, 65);

SELECT *
FROM @tblTag
PIVOT
(
    MAX([value]) FOR [Tagindex] IN ([0], [1], [2]) 
) PVT;

You need to say exactly which are the PIVOT columns. If you are going to have different values for the TagIndex and you cannot hard-coded them, you need to use dynamic PIVOT.

Also, you need to be sure you have a way to group the tagIndex values in one row. For example, different date (as in my test data), ID column which is marking when a row is inserted or something else (group ID column or date added column).

Upvotes: 1

Related Questions