Show same columns with different values (and empty values)
I am new to SQL and I am having problems.
CREATE TABLE #TempTable
(WeekNr int,
Name varchar(30),
Value int)
INSERT INTO #TempTable VALUES (21,'John',100)
INSERT INTO #TempTable VALUES (21,'Michael',133)
INSERT INTO #TempTable VALUES (21,'Tony',50)
INSERT INTO #TempTable VALUES (22,'John',80)
INSERT INTO #TempTable VALUES (23,'Michael',188)
INSERT INTO #TempTable VALUES (23,'Tony',230)
The table shows this method:
WeekNr Name Value
21 John 100
21 Michael 133
21 Tony 50
22 John 80
23 Michael 188
23 Tony 230
I need to order the data this way:
Name Vale Name Value Name Value
John 100 John 80 Michael 188
Michael 133 Tony 230
Tony 50
where the first two columns refer to WeekNr equal to 21 and the second to WeekNr equal to 22 and then WeekNr equal to 23.
It would be better if the Null values were included so that no weeks are missed. For example, if I do something like
DELETE FROM #TempTable WHERE WeekNr = 22
It should look something like this:
Name Vale Name Value Name Value
John 100 Michael 188
Michael 133 Tony 230
Tony 50
so it's easy to export data to Excel. Should I use PIVOT or UNPIVOT?
source to share
Try it.
SELECT Max([21]) NAME,Max([id21]) Value,Max([22]) Name,Max([id22]) Value,Max([23]) Name,Max([id23]) Value
FROM (SELECT Row_number()OVER(partition BY WeekNr ORDER BY NAME) rn,
NAME,value,WeekNr,
'id' + CONVERT(VARCHAR, WeekNr) AS weeks
FROM #TempTable) a
PIVOT (Max(NAME)
FOR WeekNr IN ([21],[22],[23])) piv
PIVOT (max(value)
FOR weeks IN ([id21],[id22],[id23])) piv1
GROUP BY rn
If you want your code to run dynamically try this.
DECLARE @cols VARCHAR(max)='',
@cols1 VARCHAR(max)='',
@aggcols VARCHAR(max)='',
@aggcols1 VARCHAR(max)='',
@sql NVARCHAR(max)
SELECT @cols += ',[' + CONVERT(VARCHAR(30), weeknr)+']',
@cols1 += ',[id' + CONVERT(VARCHAR(30), weeknr)+']',
@aggcols += ',max([' + CONVERT(VARCHAR(30), weeknr)+ ']) Name',
@aggcols1 += ',max([id' + CONVERT(VARCHAR(30), weeknr)+ ']) Value'
FROM (SELECT DISTINCT WeekNr
FROM #TempTable) A
select @cols= RIGHT(@cols,len(@cols)-1)
select @cols1= RIGHT(@cols1,len(@cols1)-1)
select @aggcols= RIGHT(@aggcols,len(@aggcols)-1)
select @aggcols1= RIGHT(@aggcols1,len(@aggcols1)-1)
set @sql ='SELECT '+@aggcols+','+@aggcols1+'
FROM (SELECT Row_number()OVER(partition BY WeekNr ORDER BY NAME) rn,
NAME,value,WeekNr,
''id'' + CONVERT(VARCHAR, WeekNr) AS weeks
FROM #TempTable) a
PIVOT (Max(NAME)
FOR WeekNr IN ('+@cols+')) piv
PIVOT (max(value)
FOR weeks IN ('+@cols1+')) piv1
GROUP BY rn'
exec sp_executesql @sql
source to share
This is not what you should be doing with SQL. Don't do it with SQL , it would be difficult to develop and maintain.
SQL helps you fetch your data, you can do some presentation material, but as soon as it gets too complex, you know you have the wrong approach. You have to make a presentation like this in your front-end application
source to share
I am not an expert in SQL Server either, I am essentially new to this. I don't think this is possible in any simple way. The best I can think of is to run three different queries where you have a Where clause indicating something like this:
SELECT Name, Value
FROM #TempTable
WHERE WeekNr = 21
GO
SELECT Name, Value
FROM #TempTable
WHERE WeekNr = 22
GO
SELECT Name, Value
FROM #TempTable
WHERE WeekNr = 23
GO
Then when you run the whole query, it will give you three windows of data, but not organized exactly the way you want. The problem is that if you have hundreds or thousands of WeekNr values, it becomes nearly impossible to implement and run this way.
source to share